Laurent Schneider

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

1. Create a big emp

create table big_emp as
with t(empno,mgr) as
(select 1, null from dual
union all 
select empno+1,trunc(dbms_random.value(1+empno/10,empno))
from t 
where empno<100000)
select * from t

2. execution plan

CONNECT BY


select empno,mgr
from big_emp
connect by mgr = prior empno
start with mgr is null;

E M 
- -
1 -  
2 1 
4 2 
6 2 
8 6 

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  185      78
CONNECT BY WITH FILTERING
TABLE ACCESS FULL         BIG_EMP      1    1   61      10
HASH JOIN                              2    2  122      46
CONNECT BY PUMP
TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000

recursive WITH


with e(empno,mgr) as (
select empno, mgr 
from big_emp 
where mgr is null
union all
select f.empno,f.mgr 
from big_emp f, e 
where e.empno=f.mgr)
select empno,mgr
from e;

E M
- -
1 -  
2 1 
3 1 
4 2 
5 3 
...

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  183      78
VIEW                                   3    3  183      78
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL BIG_EMP              1    1   61      10
HASH JOIN                              2    2  122      46
RECURSIVE WITH PUMP
TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000

In this particular simple case, it seems CONNECT BY have a 1% higher cost.

3. execution time

CONNECT BY


select sum(mgr)
from 
(
select empno,mgr
from big_emp
connect by mgr = prior empno
start with mgr is null
)

SUM(MGR) 
------------
2745293877 

1 rows returned in 0.73 seconds 

recursive with


with e(empno,mgr) as (
select empno, mgr 
from big_emp 
where mgr is null
union all
select f.empno,f.mgr 
from big_emp f, e 
where e.empno=f.mgr)
select sum(mgr)
from e;

SUM(MGR) 
------------
2745293877 

1 rows returned in 1.24 seconds 

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in 8.1.7.4.

And then one day you find, ten years have got behind you.

11gR2, sql recursive with

11gR2 introduced a new mechanism to build up hierarchies.

I remembered a thread in developpez.net that reveals the dubious implementation of nocycle in 10g.

For the CONNECT BY ISLEAF, I have read the technique on amis.nl.

Ok, here is my graph

The 10g query

with o as
(
SELECT 'A' obj, 'B' link from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual)
select connect_by_root obj root,level,obj,link,
  sys_connect_by_path(obj||
'->'
||link,','),
  connect_by_iscycle,
  connect_by_isleaf
from o 
connect by nocycle obj=prior link
start with obj='A';

ROOT LEVEL O L PATH                 CYCLE  LEAF
---- ----- - - -------------------- ----- -----
A        1 A B ,A->B                    0     1
A        1 A C ,A->C                    0     0
A        2 C D ,A->C,C->D               1     0
A        3 D E ,A->C,C->D,D->E          1     1

Obviously in 10g the connect by nocycle does works well with that kind of graphs, D-C and E-E are missing and C-D and D-E are marked as cycling…

Let’s try the 11gR2 equivalency.

with o(obj,link) as
(
SELECT 'A', 'B' from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual),
t(root,lev,obj,link,path) as (
select obj,1,obj,link,cast(obj||'->'||link 
as varchar2(4000))
from o 
where obj='A'  -- START WITH
union all
select 
  t.root,t.lev+1,o.obj,o.link,
  t.path||', '||o.obj||
    '->'
    ||o.link
from t, o 
where t.link=o.obj
)
search depth first by obj set ord
cycle obj set cycle to 1 default 0
select root,lev,obj,link,path,cycle,
    case
    when (lev - lead(lev) over (order by ord)) < 0
    then 0
    else 1
    end is_leaf
 from t;

ROOT LEV  OBJ  LINK PATH                        CYCLE IS_LEAF
---- ---- ---- ---- --------------------------- ----- -------
A    1    A    B    A->B                            0       1
A    1    A    C    A->C                            0       0
A    2    C    D    A->C, C->D                      0       0
A    3    D    C    A->C, C->D, D->C                0       0
A    4    C    D    A->C, C->D, D->C, C->D          1       1
A    3    D    F    A->C, C->D, D->E                0       0
A    4    F    F    A->C, C->D, D->E, E->E          0       0
A    5    F    F    A->C, C->D, D->E, E->E, E->E    1       1

It looks good

If you exclude the rows with cycle=1, you get the six rows for the graph.

11gR2, sql recursive subquery factoring

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries

with t(x) as (select 1 from dual 
union all
select x+1 from t where x<5 )
select x from t;

X
1
2
3
4
5

with t(x,y) as (select 1 x, 1 y from dual 
union all
select x+1,y*(x+1) from t where x<5 )
select x,y "X!" from t;

X  X!
1  1
2  2
3  6
4  24
5  120

with t(r,x,y) as (select 1,1,1 from dual 
union all
select r+1,y,x+y from t where r<5)
select x fib from t

FIB 
1 
1 
2 
3 
5 

with t1(x) as (select 1 from dual 
union all
select x+1 from t1 where x<4),
t2(x,y,z) as (select x, 1,x from t1
union all 
select x,y+1,x*(y+1) from t2 where y<2)
select listagg(z,';') within group (order by x) s from t2 group by y;

S 
1;2;3;4 
2;4;6;8 

11gR2, sql series

Released as announced for 2009Q4… database, 11.2 for Solaris x86_64

Still Oracle is suffering with European Union Commission who is still blocking the Sun deal.

google news about the deal

11gR2, installation, news solaris x86_64

Good news, go there http://www.oracle.com/technology/software/products/database and there
Quick install guide

11gR2, news, unix release

This will be a killer in the Oracle forums
listagg

LISTAGG (measure_expr [, 'delimiter_expr'])
  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

for instance

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) FROM emp;
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

11gR2, sql stragg

Good news today, you can download Oracle Database 11g Release 2

11gR2, news download