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

外热呃

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