connect by and recursive with (part 2)

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
  2. 
    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
    

  3. execution plan
  4. 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.

  5. execution time
  6. 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.

One thought on “connect by and recursive with (part 2)”

  1. hi Laurent,

    very interesting !

    Using autotrace, I note that RECUSRIVE WITH does the same amount of consistent read as CONNECT BY (repeating both statements)
    ( i get
    6417 consistent gets
    for both)

    However, CONNECT BY needs
    0 db block gets
    whereas
    RECURSIVE WITH needs
    178239 db block gets

    Have you an explanation for that ?

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>