Search notes:

Oracle SQL Plan operation CONNECT BY NO FILTERING WITH START-WITH

create table tq84_cbnfwsw (
   id integer primary key,
   pid references tq84_cbnfwsw,
   val varchar2(10)
);

begin
   insert into tq84_cbnfwsw values(1, null, 'ROOT');
   insert into tq84_cbnfwsw values(2,    1, 'A');
   insert into tq84_cbnfwsw values(5,    2,   'ij');  
   insert into tq84_cbnfwsw values(6,    2,   'klm');  
   insert into tq84_cbnfwsw values(3,    1, 'B');
   insert into tq84_cbnfwsw values(7,    3,   'nop');  
   insert into tq84_cbnfwsw values(4,    1, 'C');
   insert into tq84_cbnfwsw values(8,    4,   'q');  
   insert into tq84_cbnfwsw values(9,    4,   'stuvwxyz');  
   
   dbms_stats.gather_table_stats(user, 'tq84_cbnfwsw');  
end;
/
 

explain plan for
select
   lpad(' ', level-1) || val
from
   tq84_cbnfwsw
      start with pid is null
      connect by prior id = pid;
      
select * from dbms_xplan.display(format=>'basic');
--
-- ----------------------------------------------------------------
-- | Id  | Operation                               | Name         |
-- ----------------------------------------------------------------
-- |   0 | SELECT STATEMENT                        |              |
-- |   1 |  CONNECT BY NO FILTERING WITH START-WITH|              |
-- |   2 |   TABLE ACCESS FULL                     | TQ84_CBNFWSW |
-- ----------------------------------------------------------------

See also

Plan operations

Index