SQL> set timing on
SQL> set autotrace trace exp;--由于SQL执行出来需要两小时，所以就不执行了
SQL> SELECT *
FROM (SELECT 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;
执行计划
----------------------------------------------------------
Plan hash value: 3402505179
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 68 | 27608 | 2433 (2)| 00:00:30 | | |
| 0 | SELECT STATEMENT | | 2 | 2174 | 15 (7)| 00:00:01 | | |
| 1 | LOAD AS SELECT | A0K_GG_MATERIAL_PAYMENT_140122 | | | | | | |
| 1 | SORT ORDER BY | | 2 | 2174 | 15 (7)| 00:00:01 | | |
|* 2 | VIEW | | 2 | 2174 | 15 (7)| 00:00:01 | | |
| 3 | HASH UNIQUE | | 2 | 412 | 15 (7)| 00:00:01 | | |
|* 4 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | |
| 5 | TABLE ACCESS FULL | GG_MATERIAL_CLASSIFY | 1864 | 262K| 14 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 1 | 65 | 207 (0)| 00:00:03 | | |
| 7 | TABLE ACCESS BY INDEX ROWID | GG_MATERIAL | 72 | 1512 | 24 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | RELATIONSHIP_84_FK | 72 | | 3 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_DISTRIBUTION | 1624 | 35728 | 183 (0)| 00:00:03 | ROWID | ROWID |
|* 10 | INDEX RANGE SCAN | IX_DISTRIBU_ACT_QTY01 | 144K| | 6 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GG_MATERIAL_PAYMENT"."PAYMENT_AMOUNT" IS NULL)
2 - filter("B"."PARENT_CLASSIFY_ID"='201')
4 - access("CLASSIFY_ID"=PRIOR "PARENT_CLASSIFY_ID")
filter( EXISTS (SELECT 0 FROM "GG_MATERIAL" "M","GG_DISTRIBUTION" "D" WHERE "D"."ACTUAL_QTY">0 AND "D"."DATA_AREA" LIKE '03%'
AND "M"."CLASSIFY_ID"=:B1 AND "D"."MATERIAL_ID"="M"."MATERIAL_ID"))
6 - access("D"."MATERIAL_ID"="M"."MATERIAL_ID")
8 - access("M"."CLASSIFY_ID"=:B1)
9 - filter("D"."DATA_AREA" LIKE '03%')
10 - access("D"."ACTUAL_QTY">0)
--网络上提供的方法1：修改隐含参数
SQL> alter session set "