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