利用分析函式改寫範圍判斷自關聯查詢-知識星球

精彩預告：第八屆資料技術嘉年華大會將於2018年11月16日~17日在北京市朝陽區東三環中路61號富力萬麗酒店盛大開啟。本次大會邀請網際網路領先企業的資料庫專家，國產資料庫的領軍人物，雲技術等領域的知名人士，圍繞資料、智慧、連結組織前沿議題，倡導以智慧智慧演演算法應用，發掘資料價值，以技術將企業連結到未來的戰略制高點！

社群專屬福利（99.9%的人不知道）：一分錢全場通票等你搶

最近碰到一個單條SQL執行效率不佳導致資料庫整體執行負載較高的問題。

分析、定位資料庫的主要負載是這條陳述句引起的過程相對簡單，透過AWR報告就可以比較容易的完成定位，這裡就不贅述了。

現在直接看一下這個導致效能問題的SQL陳述句，其對應的SQL REPORT統計如下：

從SQL的效能指標上看，其單次執行需要6分鐘左右，處理5萬多條記錄，邏輯度只有756，主要消耗時間在CPU上。而這裡就存在疑點，邏輯讀如此之低，而CPU時間花費又如此之高，那麼這些CPU都消耗在哪裡呢？當然這個問透過SQL的統計資訊中是找不到答案的，我們下麵關註SQL的執行計劃：

從執行計劃看，Oracle選擇了HASH JOIN ANTI，JOIN的兩張表都是T_NUM，且都採用了全表掃描，並未選擇索引。僅靠執行計劃也只等得到上面的結論，至於為什麼不選擇索引，以及為什麼執行時間過長，還需要進一步的分析。

將原SQL進行簡單脫密改寫後， SQL文字類似如下：

SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

FROM T_NUM A

WHERE NOT EXISTS (

SELECT 1

FROM T_NUM B

WHERE B.BEGIN <= A.BEGIN

AND B.END >= A.END

AND B.ROWID != A.ROWID

AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

如果分析 SQL陳述句，會發現這是一個自關聯陳述句，在BEGIN欄位長度相等的前提下，想要找到那些不存在 BEGIN比當前記錄 BEGIN小且 END比當前記錄 END大的記錄。

簡單一點說，表中的記錄表示的是由 BEGIN開始到 END截至的範圍，那麼當前想要獲取的結果是找出哪些沒有範圍所包含的範圍。需要註意的是，對於當前的 SQL邏輯，如果存在兩條範圍完全相同的記錄，那麼最終這兩條記錄都會被捨棄。

業務的邏輯並不是特別複雜，但是要解決一條記錄與其他記錄進行比較，多半採用的方法是自關聯，而在這個自關聯中，既有大於等於又有小於等於，還有不等於，僅有的一個等於的關聯條件，來自範圍段 BEGIN的長度的比較。

顯而易見的是，如果是範圍段本身的比較，其選擇度一般還是不錯的，但是如果只是比較其長度，那麼無疑容易產生大量的重覆，比如在這個例子中：

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;

LENGTH(BEGIN)   COUNT(*)

————- ———-

12      22096

11       9011

13       8999

14       8186

16         49

9         45

8         41

7         27

大量重覆的資料出現在長度為11到14的範圍上，在這種情況下，僅有的一個等值判斷條件 LENGTH(BEGIN)是非常低效的，這時一條記錄根據這個等值條件會關聯到近萬條記錄，甚至關聯到兩萬多條記錄，顯然大量的實踐消耗在低效的連線過程中。

再來看一下具體的 SQL陳述句，會發現幾乎沒有辦法建立索引，因為LENGTH(BEGIN)的選擇度非常查，而其他的條件都是不等查詢，選擇度也不會好，即使建立索引，強制執行選擇索引，效率也不會好。

那麼如果想要繼續最佳化這個SQL，就只剩下一個辦法，那就是SQL的改寫。對於自關聯查詢而言，最佳的改寫方法是利用分析函式，其強大的行級處理能力，可以在一次掃描過程中獲得一條記錄與其他記錄的關係，從而消除了自關聯的必要性。

SQL改寫結果如下：

SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

FROM (

SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

FROM

(

SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

FROM T_NUM

)

)

WHERE RN = 1

AND CN = 1;

簡單的說，內層的分析函式MAX用來根據BEGIN從小到大，END從大到小的條件，確定每個範圍對應的最大的END的值。而外層的兩個分析函式，COUNT用來去掉完全重覆的記錄，而ROW_NUMBER用來獲取範圍最大的記錄（也就是沒有被其他記錄的範圍所涵蓋）。

改寫後，這個 SQL避免了自關聯，也就不存在關聯條件重覆值過高的效能隱患了。在模擬環境中，效能對比如下：

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

2 FROM T_NUM A

3 WHERE NOT EXISTS (

4     SELECT 1

5     FROM T_NUM B

6     WHERE B.BEGIN <= A.BEGIN

7     AND B.END >= A.END

8     AND B.ROWID != A.ROWID

9     AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))

10 ;

48344 rows selected.

Elapsed: 00:00:57.68

Execution Plan

———————————————————-

Plan hash value: 2540751655

————————————————————————————

| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |

————————————————————————————

|   0 | SELECT STATEMENT   |       | 48454 | 1703K|       |   275   (1)| 00:00:04 |

|* 1 | HASH JOIN ANTI    |       | 48454 | 1703K| 1424K|   275   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL| T_NUM | 48454 |   851K|       |    68   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T_NUM | 48454 |   851K|       |    68   (0)| 00:00:01 |

————————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))

filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND

“B”.ROWID<>”A”.ROWID)

Statistics

———————————————————-

0 recursive calls

0 db block gets

404 consistent gets

0 physical reads

0 redo size

2315794 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

48344 rows processed

SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

2 FROM (

3     SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

4             ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

5     FROM

6     (

7             SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

8             FROM T_NUM

9     )

10 )

11 WHERE RN = 1

12 AND CN = 1;

48344 rows selected.

Elapsed: 00:00:00.72

Execution Plan

———————————————————-

Plan hash value: 1546715670

——————————————————————————————

| Id | Operation                | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT         |       | 48454 | 2460K|       |   800   (1)| 00:00:10 |

|* 1 | VIEW                    |       | 48454 | 2460K|       |   800   (1)| 00:00:10 |

|* 2 |   WINDOW SORT PUSHED RANK|       | 48454 | 1845K| 2480K|   800   (1)| 00:00:10 |

|   3 |    WINDOW BUFFER         |       | 48454 | 1845K|       |   800   (1)| 00:00:10 |

|   4 |     VIEW                 |       | 48454 | 1845K|       |   311   (1)| 00:00:04 |

|   5 |      WINDOW SORT         |       | 48454 |   662K| 1152K|   311   (1)| 00:00:04 |

|   6 |       TABLE ACCESS FULL | T_NUM | 48454 |   662K|       |    68   (0)| 00:00:01 |

——————————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter(“RN”=1 AND “CN”=1)

2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”

ORDER BY “BEGIN”)<=1)

Statistics

———————————————————-

0 recursive calls

0 db block gets

202 consistent gets

0 physical reads

0 redo size

1493879 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

48344 rows processed