一位哥們(之前的同事，某世界500強，你懂的)問我，老外要他優化SQL，一共4個，全是樹形查詢的。前面3個跑半個多小時，后面一個跑1個小時，總共要跑 2個半小時。 問有啥方法可以優化。
因為SQL 類型基本上一樣，這里指貼一個SQL
其實這些SQL 都是 insert into ... select ....
要想INSERT 快，就必須SELECT 塊
SQL語句如下
?select rownum, adn, zdn, 'cable'
??? from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
??????????? from AGGR_1 t
?????????? where t.tdl_operation <> 2
???????????? and exists (select 1
??????????????????? from CABLE_1 a
?????????????????? where a.tdl_operation <> 2
???????????????????? and a.tdl_dn = t.tdl_z_dn)
?????????? start with exists (select 1
???????????????????????? from RESOURCE_FACING_SERVICE1_1 b
??????????????????????? where b.tdl_operation <> 2
????????????????????????? and t.tdl_a_dn = b.tdl_dn)
????????? connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)
執行計劃如下
SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1439701716
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31125 | 59M| | 36356 (1)| 00:07:17 |
| 1 | COUNT | | | | | | |
| 2 | VIEW | | 31125 | 59M| | 36356 (1)| 00:07:17 |
| 3 | HASH UNIQUE | | 31125 | 59M| 334M| 36356 (1)| 00:07:17 |
|* 4 | FILTER | | | | | | |
|* 5 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | |
| 6 | TABLE ACCESS FULL | AGGR_1 | 171K| 4353K| | 2468 (1)| 00:00:30 |
|* 7 | TABLE ACCESS FULL | RESOURCE_FACING_SERVICE1_1 | 1 | 18 | | 137 (1)| 00:00:02 |
|* 8 | TABLE ACCESS FULL | CABLE_1 | 1 | 14 | | 205 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."TDL_OPERATION"<>2 AND EXISTS (SELECT 0 FROM "CABLE_1" "A" WHERE "A"."TDL_DN"=:B1 AND
"A"."TDL_OPERATION"<>2))
5 - access("T"."TDL_A_DN"=PRIOR "T"."TDL_Z_DN")
filter( EXISTS (SELECT 0 FROM "RESOURCE_FACING_SERVICE1_1" "B" WHERE "B"."TDL_DN"=:B1 AND "B"."TDL_OPERATION"<>2))
7 - filter("B"."TDL_DN"=:B1 AND "B"."TDL_OPERATION"<>2)
8 - filter("A"."TDL_DN"=:B1 AND "A"."TDL_OPERATION"<>2)
25 rows selected.
改寫過后的SQL 如下
??????
with a as (select /*+ materialize */ tdl_dn from CABLE_1 a where a.tdl_operation <> 2 ),
???? b as (select /*+ materialize */ tdl_dn from? RESOURCE_FACING_SERVICE1_1 b where b.tdl_operation <> 2),
???? t as (select /*+ materialize */ tdl_a_dn, tdl_z_dn,tdl_operation from AGGR_1 t )???????????????????????
?select rownum, adn, zdn, 'cable'
??? from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
??????????? from? t
?????????? where t.tdl_operation <> 2
???????????? and exists (select 1
??????????????????? from? a
?????????????????? where a.tdl_dn = t.tdl_z_dn)
?????????? start with exists (select 1
???????????????????????? from? b
??????????????????????? where? t.tdl_a_dn = b.tdl_dn)
????????? connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)
執行計劃如下
SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 705757273
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31125 | 59M| | 32045 (1)| 00:06:25 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6664_D65E0 | | | | | |
|* 3 | TABLE ACCESS FULL | CABLE_1 | 12616 | 172K| | 205 (1)| 00:00:03 |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6665_D65E0 | | | | | |
|* 5 | TABLE ACCESS FULL | RESOURCE_FACING_SERVICE1_1 | 10511 | 184K| | 137 (1)| 00:00:02 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6666_D65E0 | | | | | |
| 7 | TABLE ACCESS FULL | AGGR_1 | 171K| 4353K| | 2468 (1)| 00:00:30 |
| 8 | COUNT | | | | | | |
| 9 | VIEW | | 31125 | 59M| | 29236 (1)| 00:05:51 |
| 10 | HASH UNIQUE | | 31125 | 59M| 140M| 29236 (1)| 00:05:51 |
|* 11 | FILTER | | | | | | |
|* 12 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | |
|* 13 | HASH JOIN RIGHT SEMI | | 22778 | 22M| | 179 (3)| 00:00:03 |
| 14 | VIEW | | 10511 | 164K| | 9 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6665_D65E0 | 10511 | 164K| | 9 (0)| 00:00:01 |
| 16 | VIEW | | 171K| 168M| | 168 (2)| 00:00:03 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6666_D65E0 | 171K| 4353K| | 168 (2)| 00:00:03 |
|* 18 | HASH JOIN | | 49360 | 95M| 22M| 9874 (1)| 00:01:59 |
| 19 | CONNECT BY PUMP | | | | | | |
| 20 | VIEW | | 171K| 168M| | 168 (2)| 00:00:03 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6666_D65E0 | 171K| 4353K| | 168 (2)| 00:00:03 |
|* 22 | VIEW | | 1 | 1002 | | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6664_D65E0 | 12616 | 147K| | 8 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."TDL_OPERATION"<>2)
5 - filter("B"."TDL_OPERATION"<>2)
11 - filter("T"."TDL_OPERATION"<>2 AND EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "TDL_DN"
FROM "SYS"."SYS_TEMP_0FD9D6664_D65E0" "T1") "A" WHERE "A"."TDL_DN"=:B1))
12 - access("T"."TDL_A_DN"=PRIOR "T"."TDL_Z_DN")
13 - access("T"."TDL_A_DN"="B"."TDL_DN")
18 - access("connect$_by$_pump$_011"."prior t.tdl_z_dn "="T"."TDL_A_DN")
22 - filter("A"."TDL_DN"=:B1)
42 rows selected.
?
優化完畢之后，這些SQL，前3個能在 5分鐘內跑完，最后一個能在10分鐘內跑完， 最終一共耗時 25分鐘左右，之前是2個班小時，他終于能給 澳大利亞鳥人交差了 嘎嘎。
?
有SQL?需要優化的 歡迎加入? QQ 群 220761024
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
