Search notes:
Oracle SQL Plan operation CONNECT BY PUMP
create table tq84_cbp (
id integer primary key,
pid references tq84_cbp,
val varchar2(10)
);
begin
insert into tq84_cbp values(1, null, 'ROOT');
insert into tq84_cbp values(2, 1, 'A');
insert into tq84_cbp values(5, 2, 'ij');
insert into tq84_cbp values(6, 2, 'klm');
insert into tq84_cbp values(3, 1, 'B');
insert into tq84_cbp values(7, 3, 'nop');
insert into tq84_cbp values(4, 1, 'C');
insert into tq84_cbp values(8, 4, 'q');
insert into tq84_cbp values(9, 4, 'stuvwxyz');
dbms_stats.gather_table_stats(user, 'tq84_cbp');
end;
/
explain plan for
select /*+ connect_by_filtering */
lpad(' ', level-1) || val
from
tq84_cbp
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 WITH FILTERING| |
-- | 2 | TABLE ACCESS FULL | TQ84_CBP |
-- | 3 | HASH JOIN | |
-- | 4 | CONNECT BY PUMP | |
-- | 5 | TABLE ACCESS FULL | TQ84_CBP |
-- ----------------------------------------------
Note: the table tq84_cbp
is accessed twice.
See also
The
connect_by_filtering
hint.