【DB笔试面试588】使用with优化一条SQL语句。
【DB笔试面试588】使用with优化一条SQL语句。
小麦苗DBA宝典
发布于 2019-09-29 15:56:54
发布于 2019-09-29 15:56:54
♣
题目部分
在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程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-07-04，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读