ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to
the well known CONNECT BY clause. This blog entry shows how to implement all CONNECT BY features in Recursive WITH clause.
CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):
rajesh@ORA11G> select empno,ename,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G> with r(empno,ename,mgr) as
2 ( select empno,ename,mgr
3 from emp
4 where mgr is null
5 union all
6 select e.empno,e.ename,r.empno
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7698 BLAKE 7839
7782 CLARK 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7788 SCOTT 7566
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
7369 SMITH 7902
7876 ADAMS 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.
The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.
rajesh@ORA11G> select level lvl, rpad(' ',2*level)||ename enames,empno,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 order siblings by ename;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result can be achieved using the recursive WITH clause with the SEARCH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(empno,ename,mgr,lvl) as
2 ( select empno,ename,mgr,1 as lvl
3 from emp
4 where mgr is null
5 union all
6 select e.empno, e.ename, e.mgr, lvl+1
7 from emp e, r
8 where e.mgr = r.empno
9 )
10 search depth first by ename set ord1
11 select lvl, rpad(' ',2*lvl)||ename as enames, empno,mgr
12 from r
13 order by ord1;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.02
rajesh@ORA11G>
SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children.
CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy.
rajesh@ORA11G> select ename, empno, mgr,
2 sys_connect_by_path(ename,'/') path,
3 connect_by_root ename
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr ;
ENAME EMPNO MGR PATH CONNECT_BY
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
FORD 7902 7566 /KING/JONES/FORD KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
BLAKE 7698 7839 /KING/BLAKE KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
CLARK 7782 7839 /KING/CLARK KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(ename,empno,mgr,path,by_root) as
2 ( select ename,empno,mgr,
3 '/'||ename as path,
4 ename as by_root
5 from emp
6 where mgr is null
7 union all
8 select e.ename, e.empno, e.mgr,
9 r.path||'/'||e.ename as path,
10 r.by_root
11 from emp e, r
12 where e.mgr = r.empno )
13 select ename,empno,mgr,path,by_root from r ;
ENAME EMPNO MGR PATH BY_ROOT
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
BLAKE 7698 7839 /KING/BLAKE KING
CLARK 7782 7839 /KING/CLARK KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
FORD 7902 7566 /KING/JONES/FORD KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
14 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.
rajesh@ORA11G>
rajesh@ORA11G> UPDATE emp
2 SET mgr = 7499
3 WHERE empno = 7839 ;
1 row updated.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> select level, empno,ename
2 from emp
3 start with empno = 7839
4 connect by prior empno = mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Recursive suquery factoring throws a different error code with a similar message
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename) as
2 ( select 1 as lvl, empno, ename
3 from emp
4 where empno = 7839
5 union all
6 select lvl+1, e.empno, e.ename
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r ;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles
rajesh@ORA11G> select level, empno,ename,mgr,connect_by_iscycle,
2 sys_connect_by_path(ename,'/') path
3 from emp
4 start with empno = 7839
5 connect by nocycle prior empno = mgr;
LEVEL EMPNO ENAME MGR CONNECT_BY_ISCYCLE PATH
---------- ---------- ---------- ---------- ------------------ -----------------------------
1 7839 KING 7499 0 /KING
2 7566 JONES 7839 0 /KING/JONES
3 7788 SCOTT 7566 0 /KING/JONES/SCOTT
4 7876 ADAMS 7788 0 /KING/JONES/SCOTT/ADAMS
3 7902 FORD 7566 0 /KING/JONES/FORD
4 7369 SMITH 7902 0 /KING/JONES/FORD/SMITH
2 7698 BLAKE 7839 0 /KING/BLAKE
3 7499 ALLEN 7698 1 /KING/BLAKE/ALLEN
3 7521 WARD 7698 0 /KING/BLAKE/WARD
3 7654 MARTIN 7698 0 /KING/BLAKE/MARTIN
3 7844 TURNER 7698 0 /KING/BLAKE/TURNER
3 7900 JAMES 7698 0 /KING/BLAKE/JAMES
2 7782 CLARK 7839 0 /KING/CLARK
3 7934 MILLER 7782 0 /KING/CLARK/MILLER
14 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
The Recursive WITH clause also offers clause to handle this way.
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename,mgr,path) as
2 ( select 1 as lvl, empno, ename,
3 mgr,'/'||ename as path
4 from emp
5 where empno = 7839
6 union all
7 select lvl+1, e.empno, e.ename,
8 e.mgr,r.path||'/'||e.ename
9 from emp e, r
10 where e.mgr = r.empno )
11 search depth first by ename set ord1
12 cycle empno set y_cycle to 1 default 0
13 select lvl,empno,ename,mgr,path,y_cycle from r ;
LVL EMPNO ENAME MGR PATH Y
---------- ---------- ---------- ---------- ------------------------------ -
1 7839 KING 7499 /KING 0
2 7698 BLAKE 7839 /KING/BLAKE 0
3 7499 ALLEN 7698 /KING/BLAKE/ALLEN 0
4 7839 KING 7499 /KING/BLAKE/ALLEN/KING 1
3 7900 JAMES 7698 /KING/BLAKE/JAMES 0
3 7654 MARTIN 7698 /KING/BLAKE/MARTIN 0
3 7844 TURNER 7698 /KING/BLAKE/TURNER 0
3 7521 WARD 7698 /KING/BLAKE/WARD 0
2 7782 CLARK 7839 /KING/CLARK 0
3 7934 MILLER 7782 /KING/CLARK/MILLER 0
2 7566 JONES 7839 /KING/JONES 0
3 7902 FORD 7566 /KING/JONES/FORD 0
4 7369 SMITH 7902 /KING/JONES/FORD/SMITH 0
3 7788 SCOTT 7566 /KING/JONES/SCOTT 0
4 7876 ADAMS 7788 /KING/JONES/SCOTT/ADAMS 0
15 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):
rajesh@ORA11G> select empno,ename,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G> with r(empno,ename,mgr) as
2 ( select empno,ename,mgr
3 from emp
4 where mgr is null
5 union all
6 select e.empno,e.ename,r.empno
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7698 BLAKE 7839
7782 CLARK 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7788 SCOTT 7566
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
7369 SMITH 7902
7876 ADAMS 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.
The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.
rajesh@ORA11G> select level lvl, rpad(' ',2*level)||ename enames,empno,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 order siblings by ename;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result can be achieved using the recursive WITH clause with the SEARCH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(empno,ename,mgr,lvl) as
2 ( select empno,ename,mgr,1 as lvl
3 from emp
4 where mgr is null
5 union all
6 select e.empno, e.ename, e.mgr, lvl+1
7 from emp e, r
8 where e.mgr = r.empno
9 )
10 search depth first by ename set ord1
11 select lvl, rpad(' ',2*lvl)||ename as enames, empno,mgr
12 from r
13 order by ord1;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.02
rajesh@ORA11G>
SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children.
CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy.
rajesh@ORA11G> select ename, empno, mgr,
2 sys_connect_by_path(ename,'/') path,
3 connect_by_root ename
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr ;
ENAME EMPNO MGR PATH CONNECT_BY
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
FORD 7902 7566 /KING/JONES/FORD KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
BLAKE 7698 7839 /KING/BLAKE KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
CLARK 7782 7839 /KING/CLARK KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(ename,empno,mgr,path,by_root) as
2 ( select ename,empno,mgr,
3 '/'||ename as path,
4 ename as by_root
5 from emp
6 where mgr is null
7 union all
8 select e.ename, e.empno, e.mgr,
9 r.path||'/'||e.ename as path,
10 r.by_root
11 from emp e, r
12 where e.mgr = r.empno )
13 select ename,empno,mgr,path,by_root from r ;
ENAME EMPNO MGR PATH BY_ROOT
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
BLAKE 7698 7839 /KING/BLAKE KING
CLARK 7782 7839 /KING/CLARK KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
FORD 7902 7566 /KING/JONES/FORD KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
14 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.
rajesh@ORA11G>
rajesh@ORA11G> UPDATE emp
2 SET mgr = 7499
3 WHERE empno = 7839 ;
1 row updated.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> select level, empno,ename
2 from emp
3 start with empno = 7839
4 connect by prior empno = mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Recursive suquery factoring throws a different error code with a similar message
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename) as
2 ( select 1 as lvl, empno, ename
3 from emp
4 where empno = 7839
5 union all
6 select lvl+1, e.empno, e.ename
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r ;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles
rajesh@ORA11G> select level, empno,ename,mgr,connect_by_iscycle,
2 sys_connect_by_path(ename,'/') path
3 from emp
4 start with empno = 7839
5 connect by nocycle prior empno = mgr;
LEVEL EMPNO ENAME MGR CONNECT_BY_ISCYCLE PATH
---------- ---------- ---------- ---------- ------------------ -----------------------------
1 7839 KING 7499 0 /KING
2 7566 JONES 7839 0 /KING/JONES
3 7788 SCOTT 7566 0 /KING/JONES/SCOTT
4 7876 ADAMS 7788 0 /KING/JONES/SCOTT/ADAMS
3 7902 FORD 7566 0 /KING/JONES/FORD
4 7369 SMITH 7902 0 /KING/JONES/FORD/SMITH
2 7698 BLAKE 7839 0 /KING/BLAKE
3 7499 ALLEN 7698 1 /KING/BLAKE/ALLEN
3 7521 WARD 7698 0 /KING/BLAKE/WARD
3 7654 MARTIN 7698 0 /KING/BLAKE/MARTIN
3 7844 TURNER 7698 0 /KING/BLAKE/TURNER
3 7900 JAMES 7698 0 /KING/BLAKE/JAMES
2 7782 CLARK 7839 0 /KING/CLARK
3 7934 MILLER 7782 0 /KING/CLARK/MILLER
14 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
The Recursive WITH clause also offers clause to handle this way.
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename,mgr,path) as
2 ( select 1 as lvl, empno, ename,
3 mgr,'/'||ename as path
4 from emp
5 where empno = 7839
6 union all
7 select lvl+1, e.empno, e.ename,
8 e.mgr,r.path||'/'||e.ename
9 from emp e, r
10 where e.mgr = r.empno )
11 search depth first by ename set ord1
12 cycle empno set y_cycle to 1 default 0
13 select lvl,empno,ename,mgr,path,y_cycle from r ;
LVL EMPNO ENAME MGR PATH Y
---------- ---------- ---------- ---------- ------------------------------ -
1 7839 KING 7499 /KING 0
2 7698 BLAKE 7839 /KING/BLAKE 0
3 7499 ALLEN 7698 /KING/BLAKE/ALLEN 0
4 7839 KING 7499 /KING/BLAKE/ALLEN/KING 1
3 7900 JAMES 7698 /KING/BLAKE/JAMES 0
3 7654 MARTIN 7698 /KING/BLAKE/MARTIN 0
3 7844 TURNER 7698 /KING/BLAKE/TURNER 0
3 7521 WARD 7698 /KING/BLAKE/WARD 0
2 7782 CLARK 7839 /KING/CLARK 0
3 7934 MILLER 7782 /KING/CLARK/MILLER 0
2 7566 JONES 7839 /KING/JONES 0
3 7902 FORD 7566 /KING/JONES/FORD 0
4 7369 SMITH 7902 /KING/JONES/FORD/SMITH 0
3 7788 SCOTT 7566 /KING/JONES/SCOTT 0
4 7876 ADAMS 7788 /KING/JONES/SCOTT/ADAMS 0
15 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
The difference between CONNECT BY and RECURSIVE WITH cycle
detection is that with RECURSIVE WITH the cycle is decteted after the next
recursion level wass processed. The erroneous node is repeated and the cycle
flag is set one level lower than the CONNECT_BY_ISCYCLE pseudo column.
Hi Rajesh,
ReplyDeleteRegarding your post "CONNECT PRIOR to WITH RECURSION"
That's really great post.
I have similar query:
Oracle : 11.2.0.3.3
Below Query is running extremely slow in Oracle 11203 after we have migrated from 10204.
Execution time has increased from few minutes to 8 Hrs and more.
TABLE_1 is having 6 Million records
TABLE_2 is having 7K records.
SELECT DISTINCT COL_ID, COL2_ID
FROM ( SELECT CONNECT_BY_ROOT TABLE_1.COL_ID AS COL_ID,
TABLE_1.COL2_ID AS COL2_ID
FROM TABLE_1
WHERE TABLE_1.COL_ID IN (SELECT GC.CUS_COL_ID
FROM TABLE_2 GC)
AND TABLE_1.STEP = 1
CONNECT BY NOCYCLE TABLE_1.COL_ID = PRIOR TABLE_1.COL2_ID
AND TABLE_1.COL_ID != TABLE_1.COL2_ID
AND TABLE_1.STEP = 1
AND LEVEL <= 5);
Any help in improving the performance would be highly appreciated.
@Ganesh:
ReplyDeleteyour query doesn't have "start with" clause. Is that really not needed without that all records in TABLE_1 (that is 7M) records will be considered to build this tree.
If you have access to Tkprof we can figure out bottleneck easily.
How to write same statement in oracle 10g
ReplyDeletewith r(empno,ename,mgr) as
2 ( select empno,ename,mgr
3 from emp
4 where mgr is null
5 union all
6 select e.empno,e.ename,r.empno
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r;
@Hrishikesh RodeJune:
DeleteRecursive WITH clause are applicable for 11gR2 only, we cannot achieve the same in Oracle 10g.
@Hrishikesh RodeJune: Here is the equivalent functionality in 10g using connect by construct.
ReplyDeleterajesh@ORA11G> select empno,ename,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr ;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Elapsed: 00:00:00.06
rajesh@ORA11G>