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

浏览数：20 / 时间：2015年06月12日

有一个递归查询在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 - Production

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 | 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