CONNECT BY oddity

This week I’ve seen an issue with a CONNECT BY query: for some reason Oracle 10.2.0.4 decided to build a weird plan (the query is weird too, but that’s not my point here :)). An explanation of why that happened looks interesting, so here it is.


Set up:

drop table t2 cascade constraints purge;
drop table t1 cascade constraints purge;

create table t1 (
    id int,
    parent_id int,
    pad varchar2(100),
    constraint t1_pk primary key (id),
    constraint t1_fk foreign key (parent_id) references t1
);

create table t2 (
    id1 int not null references t1,
    id2 int not null references t1,
    pad varchar2(20)
);

insert into t1
with t as (select /*+ materialize */ null from all_objects where rownum <= 1000)
select rownum,
       case when mod(rownum, 2) = 0 then rownum-1
            when mod(rownum, 5) = 0 then rownum-1
            when mod(rownum, 7) = 0 then rownum-1
            else null
       end,
       lpad('x', 100, 'x')
  from t t11, t t12
 where rownum <= 1e6;

create index t1_indx on t1(parent_id);

insert into t2
with t as (select /*+ materialize */ null from all_objects where rownum <= 1000)
select trunc(dbms_random.value(1, 1e6)),
       trunc(dbms_random.value(1, 1e6)),
       lpad('x', 20, 'x')
  from t t11, t t12
 where rownum <= 1e5;

create index t2_indx on t2(id1, id2);

exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, estimate_percent=>null, method_opt=>'for all columns size 1')
exec dbms_stats.gather_table_stats(user, 't2', cascade=>true, estimate_percent=>null, method_opt=>'for all columns size 1')

T1 & T2 are big tables and 99.(9)% of the time you want to access them by index. T1 stores some entities in hierarchies, plus there’s many-to-many relationship and table T2 is there to make it.

select blocks from user_tables where table_name = 'T1';

alter session set events '10053 trace name context forever';

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where id in
     (select /*+ qb_name(conn_by) */ id
        from t1
       start with id in (select /*+ qb_name(subq_1) */ t2.id2 from t2 where t2.id1 = :1 and pad = lpad('x', 20, 'x'))
     connect by prior id = parent_id
       union
      select /*+ qb_name(subq_2) */ id from t1 where id = :2);

alter session set events '10053 trace name context off';

select * from table(dbms_xplan.display(null,null,'basic rows cost predicate'));

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where id in
     (select /*+ qb_name(conn_by) */ id
        from t1
       start with id in (select /*+ qb_name(subq_1) */ t2.id2 from t2 where t2.id1 = :1 and pad = lpad('x', 20, 'x'))
     connect by prior id = parent_id
       union all
      select /*+ qb_name(subq_2) */ id from t1 where id = :2);

select * from table(dbms_xplan.display(null,null,'basic rows cost predicate'));

Two queries are almost the same except for the UNION / UNION ALL, but this shouldn’t affect results or execution plans much since the MAIN query block uses IN subquery. Nevertheless plans differ significantly:

    BLOCKS
----------
     16217
UNION version UNION ALL version
---------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     3 |    14  (15)|
|   1 |  NESTED LOOPS                     |          |     3 |    14  (15)|
|   2 |   VIEW                            | VW_NSO_1 |     3 |     8  (25)|
|   3 |    SORT UNIQUE                    |          |     3 |     8  (50)|
|   4 |     UNION-ALL                     |          |       |            |
|*  5 |      CONNECT BY WITH FILTERING    |          |       |            |
|*  6 |       FILTER                      |          |       |            |
|*  7 |        TABLE ACCESS FULL          | T1       |     2 |     4   (0)|
|*  8 |        TABLE ACCESS BY INDEX ROWID| T2       |     1 |     2   (0)|
|*  9 |         INDEX RANGE SCAN          | T2_INDX  |     1 |     1   (0)|
|  10 |       NESTED LOOPS                |          |       |            |
|  11 |        CONNECT BY PUMP            |          |       |            |
|  12 |        TABLE ACCESS BY INDEX ROWID| T1       |     2 |     4   (0)|
|* 13 |         INDEX RANGE SCAN          | T1_INDX  |     1 |     3   (0)|
|* 14 |      INDEX UNIQUE SCAN            | T1_PK    |     1 |     2   (0)|
|  15 |   TABLE ACCESS BY INDEX ROWID     | T1       |     1 |     2   (0)|
|* 16 |    INDEX UNIQUE SCAN              | T1_PK    |     1 |     1   (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("PARENT_ID"=PRIOR "ID")
   6 - filter( EXISTS (SELECT /*+ QB_NAME ("SUBQ_1") */ 0 FROM "T2"
              "T2" WHERE "T2"."ID2"=:B1 AND "T2"."ID1"=TO_NUMBER(:1) AND
              "PAD"='xxxxxxxxxxxxxxxxxxxx'))
   7 - access("PARENT_ID"=PRIOR "ID")
   8 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
   9 - access("T2"."ID1"=TO_NUMBER(:1) AND "T2"."ID2"=:B1)
  13 - access("PARENT_ID"=PRIOR "ID")
  14 - access("ID"=TO_NUMBER(:2))
  16 - access("ID"="$nso_col_1")
-----------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     3 |    12   (0)|
|   1 |  NESTED LOOPS                       |          |     3 |    12   (0)|
|   2 |   VIEW                              | VW_NSO_1 |     3 |     6   (0)|
|   3 |    HASH UNIQUE                      |          |     3 |     6  (34)|
|   4 |     UNION-ALL                       |          |       |            |
|*  5 |      CONNECT BY WITH FILTERING      |          |       |            |
|   6 |       TABLE ACCESS BY INDEX ROWID   | T1       |       |            |
|   7 |        NESTED LOOPS                 |          |     1 |     6  (17)|
|   8 |         SORT UNIQUE                 |          |     1 |     4   (0)|
|*  9 |          TABLE ACCESS BY INDEX ROWID| T2       |     1 |     4   (0)|
|* 10 |           INDEX RANGE SCAN          | T2_INDX  |     1 |     2   (0)|
|* 11 |         INDEX UNIQUE SCAN           | T1_PK    |     1 |     1   (0)|
|  12 |       NESTED LOOPS                  |          |       |            |
|  13 |        CONNECT BY PUMP              |          |       |            |
|  14 |        TABLE ACCESS BY INDEX ROWID  | T1       |     2 |     4   (0)|
|* 15 |         INDEX RANGE SCAN            | T1_INDX  |     1 |     3   (0)|
|* 16 |      INDEX UNIQUE SCAN              | T1_PK    |     1 |     2   (0)|
|  17 |   TABLE ACCESS BY INDEX ROWID       | T1       |     1 |     2   (0)|
|* 18 |    INDEX UNIQUE SCAN                | T1_PK    |     1 |     1   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("PARENT_ID"=PRIOR "ID")
   9 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
  10 - access("T2"."ID1"=TO_NUMBER(:1))
  11 - access("ID"="T2"."ID2")
  15 - access("PARENT_ID"=PRIOR "ID")
  16 - access("ID"=TO_NUMBER(:2))
  18 - access("ID"="$nso_col_1")

As I promised – the weird plan. UNION version uses very strange and unusual way to start CONNECT BY: full scan of T1, filtered by subquery which is executed for each row returned by the FTS. Here is plan with runtime statistics:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                     |          |      1 |      3 |      1 |00:00:07.77 |    2015K|   4736 |
|   2 |   VIEW                            | VW_NSO_1 |      1 |      3 |      1 |00:00:07.77 |    2015K|   4736 |
|   3 |    SORT UNIQUE                    |          |      1 |      3 |      1 |00:00:07.77 |    2015K|   4736 |
|   4 |     UNION-ALL                     |          |      1 |        |      1 |00:00:07.77 |    2015K|   4736 |
|*  5 |      CONNECT BY WITH FILTERING    |          |      1 |        |      0 |00:00:07.77 |    2015K|   4736 |
|*  6 |       FILTER                      |          |      1 |        |      0 |00:00:07.77 |    2015K|   4736 |
|   7 |        TABLE ACCESS FULL          | T1       |      1 |      2 |   1000K|00:00:00.03 |   15982 |   4736 |
|*  8 |        TABLE ACCESS BY INDEX ROWID| T2       |   1000K|      1 |      0 |00:00:05.19 |    2000K|      0 |
|*  9 |         INDEX RANGE SCAN          | T2_INDX  |   1000K|      1 |      0 |00:00:03.70 |    2000K|      0 |
|  10 |       NESTED LOOPS                |          |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|  11 |        CONNECT BY PUMP            |          |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|  12 |        TABLE ACCESS BY INDEX ROWID| T1       |      0 |      2 |      0 |00:00:00.01 |       0 |      0 |
|* 13 |         INDEX RANGE SCAN          | T1_INDX  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 14 |      INDEX UNIQUE SCAN            | T1_PK    |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
|  15 |   TABLE ACCESS BY INDEX ROWID     | T1       |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |
|* 16 |    INDEX UNIQUE SCAN              | T1_PK    |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("PARENT_ID"=PRIOR NULL)
   6 - filter( IS NOT NULL)
   8 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
   9 - access("T2"."ID1"=100 AND "T2"."ID2"=:B1)
  13 - access("PARENT_ID"=PRIOR NULL)
  14 - access("ID"=1000)
  16 - access("ID"="$nso_col_1")

So what’s going on here? The 10053 trace event gives a clue as usual (complete trace file if you want; wordpress doesn’t accept *.txt files and will give it away as *.doc; just save it and open as txt):

Subquery Unnest
***************
SU: Considering subquery unnesting in query block MAIN (#0)
SU:   Checking validity of unnesting subquery SUBQ_1 (#0)
SU:   Transforming ANY subquery to a join.
Registered qb: SEL$8FEC1D75 0x2b528c44 (SUBQUERY UNNEST SEL$1; SUBQ_1)
  signature (): qb_name=SEL$8FEC1D75 nbfros=2 flg=0
    fro(0): flg=0 objn=41555 hint_alias="T1"@"SEL$1"
    fro(1): flg=0 objn=41557 hint_alias="T2"@"SUBQ_1"
...
Query block (2B528C44) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT 0 FROM "TIM"."T2" "T2","TIM"."T1" "T1" WHERE "T1"."ID"="T2"."ID2" AND "T2"."ID1"=:B1 AND "T2"."PAD"=LPAD('x',20,'x')
kkqvIsPrimaryForeignKey
        cfro:T2 objn:41555 col#:2 dfro:T1 dcol#:2
        prp objn:41555 num:1 ref:2
kkqverf 
        eliminate: T1
Registered qb: SEL$23BDF322 0x2b528c44 (JOIN REMOVED FROM QUERY BLOCK SEL$8FEC1D75; SEL$8FEC1D75; "T1"@"SEL$1")
  signature (): qb_name=SEL$23BDF322 nbfros=1 flg=0
    fro(0): flg=0 objn=41557 hint_alias="T2"@"SUBQ_1"
Query block (2B528C44) after join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT 0 FROM "TIM"."T2" "T2" WHERE "T2"."ID2" IS NOT NULL AND "T2"."ID1"=:B1 AND "T2"."PAD"=LPAD('x',20,'x')
Query block (2B523FA4) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."ID" "ID","T1"."PARENT_ID" "PARENT_ID","T1"."PAD" "PAD" FROM  ( (SELECT /*+ QB_NAME ("CONN_BY") */ "SYS_ALIAS_1"."ID" "$nso_col_1" FROM "TIM"."T1" "SYS_ALIAS_1" CONNECT BY PRIOR "SYS_ALIAS_1"."ID"="SYS_ALIAS_1"."PARENT_ID" START WITH "T2"."ID2" IS NOT NULL AND "T2"."ID1"=:B1 AND "T2"."PAD"=LPAD('x',20,'x'))UNION (SELECT /*+ QB_NAME ("SUBQ_2") */ "T1"."ID" FROM "TIM"."T1" "T1" WHERE "T1"."ID"=:B2)) "VW_NSO_1","TIM"."T1" "T1" WHERE "T1"."ID"="VW_NSO_1"."$nso_col_1"
Query block (2B523FA4) unchanged

At first CBO unnests SUBQ_1 block, which should allow it to utilize join between T1 and T2 to execute START WITH. And then – the interesting part – eliminates the join! Totally reasonable, but surprising. I never thought about such possibility. After the plan is built, Oracle consider join as removed:

  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$23BDF322")
      ELIMINATE_JOIN(@"SEL$8FEC1D75" "T1"@"SEL$1")
      OUTLINE_LEAF(@"SUBQ_1")
      OUTLINE_LEAF(@"CONN_BY")
      OUTLINE_LEAF(@"SUBQ_2")
      OUTLINE_LEAF(@"SET$FCA7A018")
      OUTLINE_LEAF(@"SEL$FA7A751B")
      UNNEST(@"SET$1")
      OUTLINE(@"SEL$8FEC1D75")
      UNNEST(@"SUBQ_1")
      OUTLINE(@"SUBQ_1")
      OUTLINE(@"CONN_BY")
      OUTLINE(@"SUBQ_2")
      OUTLINE(@"SET$1")
      OUTLINE(@"MAIN")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$FA7A751B" "VW_NSO_1"@"SEL$FA7A751B")
      INDEX_RS_ASC(@"SEL$FA7A751B" "T1"@"MAIN" ("T1"."ID"))
      LEADING(@"SEL$FA7A751B" "VW_NSO_1"@"SEL$FA7A751B" "T1"@"MAIN")
      USE_NL(@"SEL$FA7A751B" "T1"@"MAIN")
      INDEX(@"SUBQ_2" "T1"@"SUBQ_2" ("T1"."ID"))
      INDEX_RS_ASC(@"CONN_BY" "T1"@"CONN_BY" ("T1"."PARENT_ID"))
      INDEX_RS_ASC(@"SUBQ_1" "T2"@"SUBQ_1" ("T2"."ID1" "T2"."ID2"))
      INDEX_RS_ASC(@"SEL$23BDF322" "T2"@"SUBQ_1" ("T2"."ID1" "T2"."ID2"))
    END_OUTLINE_DATA
  */

but plan suggests it didn’t work as desired. Or maybe that wasn’t desired at all, and is just an unexpected behavior.
I wasn’t able to reproduce the case in Oracle 11.2.0.1, both plans look almost identical with minor inconsistencies (for example, unknown to me (UNIQUE) suffix of CONNECT BY WITH FILTERING in the UNION version):

UNION version UNION ALL version
---------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |     3 |    27  (15)|
|   1 |  NESTED LOOPS                           |          |       |            |
|   2 |   NESTED LOOPS                          |          |     3 |    27  (15)|
|   3 |    VIEW                                 | VW_NSO_1 |     3 |    21  (20)|
|   4 |     SORT UNIQUE                         |          |     3 |    21  (29)|
|   5 |      UNION-ALL                          |          |       |            |
|*  6 |       CONNECT BY WITH FILTERING (UNIQUE)|          |       |            |
|   7 |        NESTED LOOPS                     |          |       |            |
|   8 |         NESTED LOOPS                    |          |     1 |     7  (15)|
|   9 |          SORT UNIQUE                    |          |     1 |     4   (0)|
|* 10 |           TABLE ACCESS BY INDEX ROWID   | T2       |     1 |     4   (0)|
|* 11 |            INDEX RANGE SCAN             | T2_INDX  |     1 |     2   (0)|
|* 12 |          INDEX UNIQUE SCAN              | T1_PK    |     1 |     1   (0)|
|  13 |         TABLE ACCESS BY INDEX ROWID     | T1       |     1 |     2   (0)|
|  14 |        NESTED LOOPS                     |          |     1 |    10  (10)|
|  15 |         CONNECT BY PUMP                 |          |       |            |
|  16 |         TABLE ACCESS BY INDEX ROWID     | T1       |     1 |     3   (0)|
|* 17 |          INDEX RANGE SCAN               | T1_INDX  |     1 |     2   (0)|
|* 18 |       INDEX UNIQUE SCAN                 | T1_PK    |     1 |     2   (0)|
|* 19 |    INDEX UNIQUE SCAN                    | T1_PK    |     1 |     1   (0)|
|  20 |   TABLE ACCESS BY INDEX ROWID           | T1       |     1 |     2   (0)|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("PARENT_ID"=PRIOR "ID")
  10 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
  11 - access("T2"."ID1"=TO_NUMBER(:1))
  12 - access("ID"="T2"."ID2")
  17 - access("connect$_by$_pump$_006"."prior id "="PARENT_ID")
       filter("PARENT_ID" IS NOT NULL)
  18 - access("ID"=TO_NUMBER(:2))
  19 - access("ID"="ID")
------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |     3 |    25   (8)|
|   1 |  NESTED LOOPS                        |          |       |            |
|   2 |   NESTED LOOPS                       |          |     3 |    25   (8)|
|   3 |    VIEW                              | VW_NSO_1 |     3 |    21  (20)|
|   4 |     HASH UNIQUE                      |          |     3 |    21  (29)|
|   5 |      UNION-ALL                       |          |       |            |
|*  6 |       CONNECT BY WITH FILTERING      |          |       |            |
|   7 |        NESTED LOOPS                  |          |       |            |
|   8 |         NESTED LOOPS                 |          |     1 |     7  (15)|
|   9 |          SORT UNIQUE                 |          |     1 |     4   (0)|
|* 10 |           TABLE ACCESS BY INDEX ROWID| T2       |     1 |     4   (0)|
|* 11 |            INDEX RANGE SCAN          | T2_INDX  |     1 |     2   (0)|
|* 12 |          INDEX UNIQUE SCAN           | T1_PK    |     1 |     1   (0)|
|  13 |         TABLE ACCESS BY INDEX ROWID  | T1       |     1 |     2   (0)|
|  14 |        NESTED LOOPS                  |          |     1 |    10  (10)|
|  15 |         CONNECT BY PUMP              |          |       |            |
|  16 |         TABLE ACCESS BY INDEX ROWID  | T1       |     1 |     3   (0)|
|* 17 |          INDEX RANGE SCAN            | T1_INDX  |     1 |     2   (0)|
|* 18 |       INDEX UNIQUE SCAN              | T1_PK    |     1 |     2   (0)|
|* 19 |    INDEX UNIQUE SCAN                 | T1_PK    |     1 |     1   (0)|
|  20 |   TABLE ACCESS BY INDEX ROWID        | T1       |     1 |     2   (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("PARENT_ID"=PRIOR "ID")
  10 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
  11 - access("T2"."ID1"=TO_NUMBER(:1))
  12 - access("ID"="T2"."ID2")
  17 - access("connect$_by$_pump$_006"."prior id "="PARENT_ID")
       filter("PARENT_ID" IS NOT NULL)
  18 - access("ID"=TO_NUMBER(:2))
  19 - access("ID"="ID")

For completeness, here are plans of the queries rewritten using new 11gR2 style – recursive subquery factoring clause:

with h(id, parent_id) as
    (select id, parent_id
       from t1
      where id in (select /*+ qb_name(subq_1) */ t2.id2 from t2 where t2.id1 = 100 and pad = lpad('x', 20, 'x'))
     union all
     select t1.id, t1.parent_id
       from h, t1
      where t1.parent_id = h.id
    )
select * from t1 
 where id in (select id from h
              union
              select id from t1 where id = :2);
UNION version UNION ALL version
----------------------------------------------------------------------------------------
| Id  | Operation                                      | Name     | Rows  | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |          |     3 |    25  (16)|
|   1 |  NESTED LOOPS                                  |          |       |            |
|   2 |   NESTED LOOPS                                 |          |     3 |    25  (16)|
|   3 |    VIEW                                        | VW_NSO_1 |     3 |    19  (22)|
|   4 |     SORT UNIQUE                                |          |     3 |    19  (32)|
|   5 |      UNION-ALL                                 |          |       |            |
|   6 |       VIEW                                     |          |     2 |    15  (14)|
|   7 |        UNION ALL (RECURSIVE WITH) BREADTH FIRST|          |       |            |
|   8 |         NESTED LOOPS                           |          |     1 |     6  (17)|
|   9 |          SORT UNIQUE                           |          |     1 |     4   (0)|
|* 10 |           TABLE ACCESS BY INDEX ROWID          | T2       |     1 |     4   (0)|
|* 11 |            INDEX RANGE SCAN                    | T2_INDX  |     1 |     2   (0)|
|* 12 |          INDEX UNIQUE SCAN                     | T1_PK    |     1 |     1   (0)|
|  13 |         NESTED LOOPS                           |          |       |            |
|  14 |          NESTED LOOPS                          |          |     1 |     9  (12)|
|  15 |           RECURSIVE WITH PUMP                  |          |       |            |
|* 16 |           INDEX RANGE SCAN                     | T1_INDX  |     1 |     2   (0)|
|  17 |          TABLE ACCESS BY INDEX ROWID           | T1       |     1 |     3   (0)|
|* 18 |       INDEX UNIQUE SCAN                        | T1_PK    |     1 |     2   (0)|
|* 19 |    INDEX UNIQUE SCAN                           | T1_PK    |     1 |     1   (0)|
|  20 |   TABLE ACCESS BY INDEX ROWID                  | T1       |     1 |     2   (0)|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
  11 - access("T2"."ID1"=100)
  12 - access("ID"="T2"."ID2")
  16 - access("T1"."PARENT_ID"="H"."ID")
       filter("T1"."PARENT_ID" IS NOT NULL)
  18 - access("ID"=TO_NUMBER(:2))
  19 - access("ID"="ID")
----------------------------------------------------------------------------------------
| Id  | Operation                                      | Name     | Rows  | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |          |     3 |    23   (9)|
|   1 |  NESTED LOOPS                                  |          |       |            |
|   2 |   NESTED LOOPS                                 |          |     3 |    23   (9)|
|   3 |    VIEW                                        | VW_NSO_1 |     3 |    17  (12)|
|   4 |     HASH UNIQUE                                |          |     3 |    17  (24)|
|   5 |      UNION-ALL                                 |          |       |            |
|   6 |       VIEW                                     |          |     2 |    15  (14)|
|   7 |        UNION ALL (RECURSIVE WITH) BREADTH FIRST|          |       |            |
|   8 |         NESTED LOOPS                           |          |     1 |     6  (17)|
|   9 |          SORT UNIQUE                           |          |     1 |     4   (0)|
|* 10 |           TABLE ACCESS BY INDEX ROWID          | T2       |     1 |     4   (0)|
|* 11 |            INDEX RANGE SCAN                    | T2_INDX  |     1 |     2   (0)|
|* 12 |          INDEX UNIQUE SCAN                     | T1_PK    |     1 |     1   (0)|
|  13 |         NESTED LOOPS                           |          |       |            |
|  14 |          NESTED LOOPS                          |          |     1 |     9  (12)|
|  15 |           RECURSIVE WITH PUMP                  |          |       |            |
|* 16 |           INDEX RANGE SCAN                     | T1_INDX  |     1 |     2   (0)|
|  17 |          TABLE ACCESS BY INDEX ROWID           | T1       |     1 |     3   (0)|
|* 18 |       INDEX UNIQUE SCAN                        | T1_PK    |     1 |     2   (0)|
|* 19 |    INDEX UNIQUE SCAN                           | T1_PK    |     1 |     1   (0)|
|  20 |   TABLE ACCESS BY INDEX ROWID                  | T1       |     1 |     2   (0)|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter("PAD"='xxxxxxxxxxxxxxxxxxxx')
  11 - access("T2"."ID1"=100)
  12 - access("ID"="T2"."ID2")
  16 - access("T1"."PARENT_ID"="H"."ID")
       filter("T1"."PARENT_ID" IS NOT NULL)
  18 - access("ID"=TO_NUMBER(:2))
  19 - access("ID"="ID")

Update: seems to be a bug 6429113.

About these ads

One Trackback

  1. [...] 2-Different behavior for connect by with UNION and UNION ALL clauses due to bug 6429113 Timur Akhmadeev-connect by oddity [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 288 other followers