【DB筆試面試588】使用with優化一條SQL語句。
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，使用with優化一條SQL語句。
♣
答案部分
當面試官問到這類問題時，讀者可以根據自己的經驗談談是自己是如何優化曾經碰到的SQL語句的即可，下面作者會給出一些自己曾遇到過很多次的SQL優化的案例。
在SQL優化中，有一個很重要的原則就是減少對大表的查詢次數，尤其是要避免在同一個SQL中多次掃描同一張大表，若有這種情況可以考慮SQL改寫，下面給出幾種常見的改寫方式：
先根據條件提取數據到臨時表中，然後再做連接，即利用WITH語句來改寫SQL。使用WITH子查詢的優點就在於其複雜查詢語句只需要執行一次，但結果可以在同一個查詢語句中被多次使用。
① 有的相似的語句可以用MAX+DECODE函數來處理。
② 有子查詢的SQL應該避免子查詢掃描同一張表。
下面通過一個例子來說明如何通過WITH來優化SQL：
LHR@DLHR> EXPLAIN PLAN FOR SELECT A.ID, B.TIME 2 FROM T_NEW A 3 JOIN T B 4 ON (A.ID = B.ID) 5 JOIN T_OLD_1 C 6 ON (A.ID = C.ID AND B.ID=C.ID) 7 UNION ALL 8 SELECT A.ID, B.TIME 9 FROM T_NEW A 10 JOIN T B 11 ON A.ID = B.ID 12 JOIN T_OLD_2 C 13 ON (A.ID = C.ID AND B.ID=C.ID) 14 ; Explained. LHR@DLHR> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- Plan hash value: 286044770 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 96 | | 1416 (2)| 00:00:17 | | 1 | UNION-ALL | | | | | | | |* 2 | HASH JOIN | | 1 | 48 | 2808K| 706 (2)| 00:00:09 | |* 3 | HASH JOIN | | 75517 | 1917K| 1848K| 322 (3)| 00:00:04 | | 4 | TABLE ACCESS FULL| T_NEW | 75516 | 958K| | 70 (3)| 00:00:01 | | 5 | TABLE ACCESS FULL| T_OLD_1 | 78812 | 1000K| | 66 (4)| 00:00:01 | | 6 | TABLE ACCESS FULL | T | 109K| 2362K| | 67 (5)| 00:00:01 | |* 7 | HASH JOIN | | 1 | 48 | 2808K| 710 (2)| 00:00:09 | |* 8 | HASH JOIN | | 75517 | 1917K| 1848K| 326 (3)| 00:00:04 | | 9 | TABLE ACCESS FULL| T_NEW | 75516 | 958K| | 70 (3)| 00:00:01 | | 10 | TABLE ACCESS FULL| T_OLD_2 | 82170 | 1043K| | 66 (4)| 00:00:01 | | 11 | TABLE ACCESS FULL | T | 109K| 2362K| | 67 (5)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"."ID"="C"."ID" AND "A"."ID"="B"."ID") 3 - access("A"."ID"="C"."ID") 7 - access("B"."ID"="C"."ID" AND "A"."ID"="B"."ID") 8 - access("A"."ID"="C"."ID") Note ----- - dynamic sampling used for this statement (level=2) 30 rows selected. --從執行計劃可以看出，T_NEW和T表都掃描了2次，而且每次掃描都是全表掃描，下面利用WITH進行優化： LHR@DLHR> EXPLAIN PLAN FOR WITH TMP AS 2 (SELECT A.ID, B.TIME 3 FROM T_NEW A 4 JOIN T B 5 ON (A.ID = B.ID)) 6 SELECT A.ID, A.TIME 7 FROM TMP A 8 LEFT JOIN T_OLD_1 B 9 ON A.ID = B.ID 10 UNION ALL 11 SELECT A.ID, B.TIME 12 FROM TMP A 13 LEFT JOIN T_OLD_2 B 14 ON A.ID = B.ID; Explained. LHR@DLHR> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 2846196527 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 151K| 5162K| | 779 (2)| 00:00:10 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6613_F332D | | | | | | |* 3 | HASH JOIN | | 75517 | 2581K| 1848K| 408 (3)| 00:00:05 | | 4 | TABLE ACCESS FULL | T_NEW | 75516 | 958K| | 70 (3)| 00:00:01 | | 5 | TABLE ACCESS FULL | T | 109K| 2362K| | 67 (5)| 00:00:01 | | 6 | UNION-ALL | | | | | | | |* 7 | HASH JOIN RIGHT OUTER | | 75518 | 2581K| 1928K| 386 (2)| 00:00:05 | | 8 | TABLE ACCESS FULL | T_OLD_1 | 78812 | 1000K| | 66 (4)| 00:00:01 | | 9 | VIEW | | 75517 | 1622K| | 101 (2)| 00:00:02 | | 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_F332D | 75517 | 1622K| | 101 (2)| 00:00:02 | |* 11 | HASH JOIN OUTER | | 75517 | 2581K| 1848K| 393 (3)| 00:00:05 | | 12 | VIEW | | 75517 | 958K| | 101 (2)| 00:00:02 | | 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_F332D | 75517 | 1622K| | 101 (2)| 00:00:02 | | 14 | TABLE ACCESS FULL | T_OLD_2 | 82170 | 1765K| | 66 (4)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."ID"="B"."ID") 7 - access("A"."ID"="B"."ID"(+)) 11 - access("A"."ID"="B"."ID"(+)) Note ----- - dynamic sampling used for this statement (level=2) 32 rows selected. --從優化後的執行計劃可以看出，COST從原來的1416變為了現在的779，性能提升了2倍，而T和T_NEW表都各掃描了1次。
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
