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.
Plan operations such as RECURSIVE WITH PUMP

Index