Oracle11g recursion + exists Execution Plan Change

Oracle11g recursion + exists Execution Plan Change

There is a recursive query that runs fast on 10g, but cannot run on 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;-- It takes two hours to execute the SQL statement.
SQL> SELECT *
FROM (select distinct .*
FROM GG_MATERIAL_CLASSIFY
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 '000000') B
Where B. PARENT_CLASSIFY_ID = '201312'
Order by B. CODE ASC;
Execution Plan
----------------------------------------------------------
Plan hash value: 3402505179
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
| 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 |
| * 10 | index range scan | IX_DISTRIBU_ACT_QTY01 | 144K | 6 (0) | 00:00:01 |
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("GG_MATERIAL_PAYMENT". "PAYMENT_AMOUNT" is null)
2-filter ("B". "PARENT_CLASSIFY_ID" = '000000 ')
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 '000000'
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 '201312 ')
10-access ("D". "ACTUAL_QTY"> 0)
-- Method 1 provided on the Network: Modify implicit Parameters
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 .*
3 FROM GG_MATERIAL_CLASSIFY
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 '000000') B
12 where B. PARENT_CLASSIFY_ID = '201312'
13 order by B. CODE ASC;
11 rows have been selected.
Used time: 00: 00: 04.39
Execution Plan
----------------------------------------------------------
Plan hash value: 3792201725
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
| 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 |
| * 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 ||
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("B". "PARENT_CLASSIFY_ID" = '000000 ')
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 '201312 ')
11-access ("D". "ACTUAL_QTY"> 0)
15-access ("CLASSIFY_ID" = PRIOR "PARENT_CLASSIFY_ID ")
-- Method 2 provided on the Network: invalid and cannot be executed (Note: Execute with another session)
SELECT *
FROM (SELECT/* + connect_by_filtering */distinct .*
FROM GG_MATERIAL_CLASSIFY
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 '000000') B
Where B. PARENT_CLASSIFY_ID = '201312'
Order by B. CODE ASC;
To sum up the network method, it is best not to modify the implicit parameter. A maximum of Hint values are added, but the Hint value is invalid. Therefore, you can find other methods.

I accidentally changed exits to in, and the problem was solved.
SQL> set autotrace traceonly
SQL> SELECT *
FROM (select distinct .*
FROM GG_MATERIAL_CLASSIFY
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 '000000') B
Where B. PARENT_CLASSIFY_ID = '201312'
Order by B. CODE ASC;
11 rows have been selected.
Used time: 00: 00: 01.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4133877384
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |
| * 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 |
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("B". "PARENT_CLASSIFY_ID" = '000000 ')
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 '201312 ')
11-access ("D". "ACTUAL_QTY"> 0)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
113928 consistent gets
0 physical reads
0 redo size
1960 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client

I checked the difference between in and exists in generating execution plans. From the predicates, we can see that exists does not need to be expanded. So I added an Hint to verify it, the execution result is the same as in.
-- Unnest
SQL> SELECT *
FROM (select distinct .*
FROM GG_MATERIAL_CLASSIFY
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 '000000') B
Where B. PARENT_CLASSIFY_ID = '201312'
Order by B. CODE ASC;
11 rows have been selected.
Used time: 00: 00: 01.18
Execution Plan
----------------------------------------------------------
Plan hash value: 2653190462
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
| 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 |
| * 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 |
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("B". "PARENT_CLASSIFY_ID" = '000000 ')
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 '201312 ')
11-access ("D". "ACTUAL_QTY"> 0)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
113928 consistent gets
0 physical reads
0 redo size
1960 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
11 rows processed