使用with优化一条SQL语句。

DB宝 2019-07-04

787

♣题目部分在Oracle中，使用with优化一条SQL语句。

     
♣答案部分

当面试官问到这类问题时，读者可以根据自己的经验谈谈是自己是如何优化曾经碰到的SQL语句的即可，下面作者会给出一些自己曾遇到过很多次的SQL优化的案例。
在SQL优化中，有一个很重要的原则就是减少对大表的查询次数，尤其是要避免在同一个SQL中多次扫描同一张大表，若有这种情况可以考虑SQL改写，下面给出几种常见的改写方式：
先根据条件提取数据到临时表中，然后再做连接，即利用WITH语句来改写SQL。使用WITH子查询的优点就在于其复杂查询语句只需要执行一次，但结果可以在同一个查询语句中被多次使用。
①　有的相似的语句可以用MAX+DECODE函数来处理。
②　有子查询的SQL应该避免子查询扫描同一张表。
下面通过一个例子来说明如何通过WITH来优化SQL：
 1LHR@DLHR> EXPLAIN PLAN FOR SELECT A.ID, B.TIME
 2  2    FROM T_NEW A
 3  3    JOIN T B
 4  4      ON (A.ID = B.ID)
 5  5    JOIN T_OLD_1 C
 6  6      ON (A.ID = C.ID AND B.ID=C.ID)
 7  7  UNION ALL
 8  8  SELECT A.ID, B.TIME
 9  9    FROM T_NEW A
10 10    JOIN T B
11 11      ON A.ID = B.ID
12 12    JOIN T_OLD_2 C
13 13      ON (A.ID = C.ID AND B.ID=C.ID)
14 14  ;
15Explained.
16LHR@DLHR> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
17PLAN_TABLE_OUTPUT
18--------------------------------------------------------------------------------------------------
19Plan hash value: 286044770
20----------------------------------------------------------------------------------------
21| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
22----------------------------------------------------------------------------------------
23|   0 | SELECT STATEMENT     |         |     2 |    96 |       |  1416   (2)| 00:00:17 |
24|   1 |  UNION-ALL           |         |       |       |       |            |          |
25|*  2 |   HASH JOIN          |         |     1 |    48 |  2808K|   706   (2)| 00:00:09 |
26|*  3 |    HASH JOIN         |         | 75517 |  1917K|  1848K|   322   (3)| 00:00:04 |
27|   4 |     TABLE ACCESS FULL| T_NEW   | 75516 |   958K|       |    70   (3)| 00:00:01 |
28|   5 |     TABLE ACCESS FULL| T_OLD_1 | 78812 |  1000K|       |    66   (4)| 00:00:01 |
29|   6 |    TABLE ACCESS FULL | T       |   109K|  2362K|       |    67   (5)| 00:00:01 |
30|*  7 |   HASH JOIN          |         |     1 |    48 |  2808K|   710   (2)| 00:00:09 |
31|*  8 |    HASH JOIN         |         | 75517 |  1917K|  1848K|   326   (3)| 00:00:04 |
32|   9 |     TABLE ACCESS FULL| T_NEW   | 75516 |   958K|       |    70   (3)| 00:00:01 |
33|  10 |     TABLE ACCESS FULL| T_OLD_2 | 82170 |  1043K|       |    66   (4)| 00:00:01 |
34|  11 |    TABLE ACCESS FULL | T       |   109K|  2362K|       |    67   (5)| 00:00:01 |
35----------------------------------------------------------------------------------------
36
37Predicate Information (identified by operation id):
38---------------------------------------------------
39   2 - access("B"."ID"="C"."ID" AND "A"."ID"="B"."ID")
40   3 - access("A"."ID"="C"."ID")
41   7 - access("B"."ID"="C"."ID" AND "A"."ID"="B"."ID")
42   8 - access("A"."ID"="C"."ID")
43Note
44-----
45   - dynamic sampling used for this statement (level=2)
4630 rows selected.
47
48--从执行计划可以看出，T_NEW和T表都扫描了2次，而且每次扫描都是全表扫描，下面利用WITH进行优化：
49LHR@DLHR> EXPLAIN PLAN FOR WITH TMP AS
50  2   (SELECT A.ID, B.TIME
51  3    FROM T_NEW A
52  4    JOIN T B
53  5      ON (A.ID = B.ID))
54  6  SELECT A.ID, A.TIME
55  7    FROM TMP A
56  8      LEFT JOIN T_OLD_1 B 
57  9      ON A.ID = B.ID
58 10  UNION ALL
59 11  SELECT A.ID, B.TIME 
60 12     FROM TMP A 
61 13      LEFT JOIN T_OLD_2 B 
62 14      ON A.ID = B.ID; 
63Explained.
64LHR@DLHR> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
65PLAN_TABLE_OUTPUT
66----------------------------------------------------------------------------------------------------
67Plan hash value: 2846196527
68---------------------------------------------------------------------------------------------------------------
69| Id  | Operation                  | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
70---------------------------------------------------------------------------------------------------------------
71|   0 | SELECT STATEMENT           |                          |   151K|  5162K|       |   779   (2)| 00:00:10 |
72|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |       |            |          |
73|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6613_F332D |       |       |       |            |          |
74|*  3 |    HASH JOIN               |                          | 75517 |  2581K|  1848K|   408   (3)| 00:00:05 |
75|   4 |     TABLE ACCESS FULL      | T_NEW                    | 75516 |   958K|       |    70   (3)| 00:00:01 |
76|   5 |     TABLE ACCESS FULL      | T                        |   109K|  2362K|       |    67   (5)| 00:00:01 |
77|   6 |   UNION-ALL                |                          |       |       |       |            |          |
78|*  7 |    HASH JOIN RIGHT OUTER   |                          | 75518 |  2581K|  1928K|   386   (2)| 00:00:05 |
79|   8 |     TABLE ACCESS FULL      | T_OLD_1                  | 78812 |  1000K|       |    66   (4)| 00:00:01 |
80|   9 |     VIEW                   |                          | 75517 |  1622K|       |   101   (2)| 00:00:02 |
81|  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6613_F332D | 75517 |  1622K|       |   101   (2)| 00:00:02 |
82|* 11 |    HASH JOIN OUTER         |                          | 75517 |  2581K|  1848K|   393   (3)| 00:00:05 |
83|  12 |     VIEW                   |                          | 75517 |   958K|       |   101   (2)| 00:00:02 |
84|  13 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6613_F332D | 75517 |  1622K|       |   101   (2)| 00:00:02 |
85|  14 |     TABLE ACCESS FULL      | T_OLD_2                  | 82170 |  1765K|       |    66   (4)| 00:00:01 |
86---------------------------------------------------------------------------------------------------------------
87Predicate Information (identified by operation id):
88---------------------------------------------------
89   3 - access("A"."ID"="B"."ID")
90   7 - access("A"."ID"="B"."ID"(+))
91  11 - access("A"."ID"="B"."ID"(+))
92Note
93-----
94   - dynamic sampling used for this statement (level=2)
9532 rows selected.
96
97--从优化后的执行计划可以看出，COST从原来的1416变为了现在的779，性能提升了2倍，而T和T_NEW表都各扫描了1次。


本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。