SQL> alter session set "_connect_by_use_union_all" = "old_plan_mode";
SQL> SELECT *
2 FROM (SELECT DISTINCT A.*
3 FROM GG_MATERIAL_CLASSIFY A
4 CONNECT BY PRIOR PARENT_CLASSIFY_ID = CLASSIFY_ID
5 START WITH exists
6 (SELECT DISTINCT M.CLASSIFY_ID
7 FROM GG_DISTRIBUTION D, GG_MATERIAL M
8 WHERE D.MATERIAL_ID = M.MATERIAL_ID
9 AND A.CLASSIFY_ID=M.CLASSIFY_ID
10 AND D.ACTUAL_QTY > 0
11 AND D.DATA_AREA LIKE '03%')) B
12 WHERE B.PARENT_CLASSIFY_ID = '201'
13 ORDER BY B.CODE ASC;
已选择11行。
已用时间: 00: 00: 04.39
执行计划
----------------------------------------------------------
Plan hash value: 3792201725
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1087 | | 3 (34)| 00:00:01 | | |
|* 2 | VIEW | | 1 | 1087 | | 3 (34)| 00:00:01 | | |
| 3 | HASH UNIQUE | | 1 | 144 | | 3 (34)| 00:00:01 | | |
|* 4 | CONNECT BY WITH FILTERING | | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | GG_MATERIAL_CLASSIFY | | | | | | | |
|* 6 | HASH JOIN | | 114K| 5816K| | 16615 (1)| 00:03:20 | | |
| 7 | INDEX FAST FULL SCAN | PK_GG_MATERIAL_CLASSIFY | 1864 | 16776 | | 3 (0)| 00:00:01 | | |
|* 8 | HASH JOIN | | 144K| 6051K| 3784K| 16610 (1)| 00:03:20 | | |
| 9 | INDEX FAST FULL SCAN | INX_GG_MATERIAL_CLASSIFY | 117K| 2403K| | 145 (2)| 00:00:02 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_DISTRIBUTION | 144K| 3097K| | 16045 (1)| 00:03:13 | ROWID | ROWID |
|* 11 | INDEX RANGE SCAN | IX_DISTRIBU_ACT_QTY01 | 144K| | | 346 (1)| 00:00:05 | | |
| 12 | NESTED LOOPS | | | | | | | | |
| 13 | CONNECT BY PUMP | | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | GG_MATERIAL_CLASSIFY | 1 | 144 | | 2 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | PK_GG_MATERIAL_CLASSIFY | 1 | | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."PARENT_CLASSIFY_ID"='201')
4 - access("CLASSIFY_ID"=PRIOR "PARENT_CLASSIFY_ID")
6 - access("A"."CLASSIFY_ID"="M"."CLASSIFY_ID")
8 - access("D"."MATERIAL_ID"="M"."MATERIAL_ID")
10 - filter("D"."DATA_AREA" LIKE '03%')
11 - access("D"."AC