【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程序员面试笔试宝典》，作者：李华荣。
