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

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1740476108, '13.59.243.130', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/statement/execution/plan/operations/connect-by-pump(82): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78