15 - access("CLASSIFY_ID"=PRIOR "PARENT_CLASSIFY_ID")
--网络上提供的方法2：失效，执行不出来（注意，要换一个session执行）
SELECT *
FROM (SELECT /*+ connect_by_filtering */DISTINCT A.*
FROM GG_MATERIAL_CLASSIFY A
CONNECT BY PRIOR PARENT_CLASSIFY_ID = CLASSIFY_ID
START WITH exists
(SELECT DISTINCT M.CLASSIFY_ID
FROM GG_DISTRIBUTION D, GG_MATERIAL M
WHERE D.MATERIAL_ID = M.MATERIAL_ID
AND A.CLASSIFY_ID=M.CLASSIFY_ID
AND D.ACTUAL_QTY > 0
AND D.DATA_AREA LIKE '03%')) B
WHERE B.PARENT_CLASSIFY_ID = '201'
ORDER BY B.CODE ASC;
对网络的方法总结，最好不要修改隐含参数，最多加上Hint，但Hint失效，所以再去找其他的方法。
无意之中把exits改为了in，问题解决了。
SQL> set autotrace traceonly
SQL> SELECT *
FROM (SELECT DISTINCT A.*
FROM GG_MATERIAL_CLASSIFY A
CONNECT BY PRIOR PARENT_CLASSIFY_ID = CLASSIFY_ID
START WITH CLASSIFY_ID IN
(SELECT DISTINCT M.CLASSIFY_ID
FROM GG_DISTRIBUTION D, GG_MATERIAL M
WHERE D.MATERIAL_ID = M.MATERIAL_ID
AND D.DATA_AREA LIKE '03%')) B
WHERE B.PARENT_CLASSIFY_ID = '201'
ORDER BY B.CODE ASC;
已选择11行。
已用时间: 00: 00: 01.00
执行计划
----------------------------------------------------------
Plan hash value: 4133877384
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 645K| 57M| | 3895 (1)| 00:00:47 | | |
| 0 | SELECT STATEMENT | | 3246 | 3445K| | 16641 (1)| 00:03:20 | | |
| 1 | LOAD AS SELECT | A2K_GG_INVOICE_ITEM_140106 | | | | | | | |
| 1 | SORT ORDER BY | | 3246 | 3445K| | 16641 (1)| 00:03:20 | | |
| 2 | TABLE ACCESS FULL | GG_INVOICE_ITEM | 645K| 57M| | 1984 (2)| 00:00:24 | | |
|* 2 | VIEW | | 3246 | 3445K| | 16641 (1)| 00:03:20 | | |
| 3 | HASH UNIQUE | | 3246 | 653K| | 16641 (1)| 00:03:20 | | |
|* 4 | CONNECT BY WITHOUT FILTERING (UNIQUE) | | | | | | | | |
|* 5 | HASH JOIN SEMI | | 1623 | 256K| | 16626 (1)| 00:03:20 | | |
| 6 | TABLE ACCESS FULL | GG_MATERIAL_CLASSIFY | 1864 | 262K| | 14 (0)| 00:00:01 | | |
| 7 | VIEW | VW_NSO_1 | 144K| 2533K| | 16610 (1)| 00:03:20 | | |
|* 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 | TABLE ACCESS FULL |