树形查询的优化(二)
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)
执行计划如下
[html]
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)