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.

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

  1. Sokrates

    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 ?

Comments are closed.