Oracle 11g 递归+ exists执行计划的改变

有一个递归查询在Oracle 10g上运行很快，但在11g上运行不出来。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - ProductionSQL> 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 | TABLE ACCESS FULL | GG_MATERIAL_PAYMENT | 68 | 27608 | 2431 （2）| 00:00:30 | | |
|* 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 "_optimizer_connect_by_elim_dups" = false;
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 | | |
| 1 | SORT ORDER BY | | 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"."ACTUAL_QTY">0）
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.ACTUAL_QTY > 0
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 | GG_MATERIAL_CLASSIFY | 1864 | 262K| | 14 （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"）
5 - access（"CLASSIFY_ID"="CLASSIFY_ID"）
8 - access（"D"."MATERIAL_ID"="M"."MATERIAL_ID"）
10 - filter（"D"."DATA_AREA" LIKE "03%"）
11 - access（"D"."ACTUAL_QTY">0）
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
113928 consistent gets
0 physical reads
0 redo size
1960 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client 我看了一下in 和 exists产生执行计划的区别，从谓词从看到exists需要没有展开，所以我加了一个Hint验证了一下，执行结果跟in就是一样的了。
--unnest为展开子查询
SQL> SELECT *
FROM （SELECT DISTINCT A.*
FROM GG_MATERIAL_CLASSIFY A
CONNECT BY PRIOR PARENT_CLASSIFY_ID = CLASSIFY_ID
START WITH exists
（SELECT /*+unnest*/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;
已选择11行。
已用时间: 00: 00: 01.18
执行计划
----------------------------------------------------------
Plan hash value: 2653190462
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost （%CPU）| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3246 | 3445K| | 16641 （1）| 00:03:20 | | |
| 1 | SORT ORDER BY | | 3246 | 3445K| | 16641 （1）| 00:03:20 | | |
|* 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_SQ_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 | GG_MATERIAL_CLASSIFY | 1864 | 262K| | 14 （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"）
5 - access（"A"."CLASSIFY_ID"="ITEM_0"）
8 - access（"D"."MATERIAL_ID"="M"."MATERIAL_ID"）
10 - filter（"D"."DATA_AREA" LIKE "03%"）
11 - access（"D"."ACTUAL_QTY">0）
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
113928 consistent gets
0 physical reads
0 redo size
1960 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts （memory）
0 sorts （disk）
11 rows processed在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle性能优化之共享池 http://www.linuxidc.com/Linux/2012-02/54062.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址