| Stat Name | Statement Total | Per Execution | % Snap Total |
| Elapsed Time (ms) | 363,741 | 363,740.78 | 8 .42 |
| CPU Time (ms) | 362,770 | 362,770.00 | 8 .81 |
| Executions | 1 | ||
| Buffer Gets | 756 | 756.00 | 0.00 |
| Disk Reads | 0 | 0.00 | 0.00 |
| Parse Calls | 1 | 1.00 | 0.01 |
| Rows | 50,825 | 50,825.00 | |
| User I/O Wait Time (ms) | 0 | ||
| Cluster Wait Time (ms) | 0 | ||
| Application Wait Time (ms) | 0 | ||
| Concurrency Wait Time (ms) | 0 | ||
| Invalidations | 0 | ||
| Version Count | 1 | ||
| Sharable Mem(KB) | 28 |
從SQL的性能指標上看，其單次執行需要6分鐘左右，處理5萬多條記錄，邏輯度只有756，主要消耗時間在CPU上。而這里就存在疑點，邏輯讀如此之低，而CPU時間花費又如此之高，那么這些CPU都消耗在哪里呢？當然這個問通過SQL的統計信息中是找不到答案的，我們下面關注SQL的執行計劃：
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1226 (100) | |||||
| 1 | SORT ORDER BY | 49379 | 3375K | 3888K | 1226 (2) | 00:00:05 | |
| 2 | HASH JOIN ANTI | 49379 | 3375K | 2272K | 401 (3) | 00:00:02 | |
| 3 | TABLE ACCESS FULL | T_NUM | 49379 | 1687K | 88 (4) | 00:00:01 | |
| 4 | TABLE ACCESS FULL | T_NUM | 49379 | 1687K | 88 (4) | 00:00:01 |
從執行計劃看，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
原SQL運行時間接近1分鐘，而改寫后的SQL語句只需要0.72秒，執行時間變為原本的1/80，邏輯讀減少一半。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
