The simple example of recursive subquery factoring.

Maybe, recursive subquery factoring was used in few cases only. But in that case, recursive subquery factoring provides very convenient way to handle each node.

For example, there is a diagram like below and we want calculate the ela(psed time) to current node. Without the recursive subquery factoring, we shoud use sys_connect_by_path and user define function.

(1) Create a table and data loading

create table node(pnode number, cnode number, ela number);

insert into node values (1,null,2);
   insert into node values (2,1,4);
      insert into node values (5,2,4);
         insert into node values (8,5,10);
         insert into node values (9,5,20);
         insert into node values (10,5,10);
      insert into node values (6,2,6);
   insert into node values (3,1,6);
      insert into node values (7,3,8);
   insert into node values (4,1,8);

commit;

(2) The sample query that use recursive subquery factoring

with node_sum (pnode, cnode, ela_current, ela_sigma, lvl)
as (
   select pnode,
          cnode,
          ela  ela_current,
          ela  ela_sigma,
          1    lvl
   from   node
   where  pnode is null
   union all
   select c.pnode,
          c.cnode,
          c.ela ela_current,
          p.ela_sigma + c.ela ela_sigma,
          p.lvl + 1
   from   node_sum p,
          node c
   where  p.cnode = c.pnode
)
select
 lpad(' ', 2 * lvl - 2, ' ') || pnode||'->'||cnode||' Current=>'||ela_current||' Sigma=>'||ela_sigma
 from node_sum;

->1 Current=>2 Sigma=>2
  1->2 Current=>4 Sigma=>6
  1->3 Current=>6 Sigma=>8
  1->4 Current=>8 Sigma=>10
    2->5 Current=>4 Sigma=>10
    2->6 Current=>6 Sigma=>12
    3->7 Current=>8 Sigma=>16
      5->8 Current=>10 Sigma=>20
      5->9 Current=>20 Sigma=>30
      5->10 Current=>10 Sigma=>20