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


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.