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?
- Create a big emp
- execution plan
- execution time
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
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.
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.
