This article gives tips and tricks on how to write and understand hierarchical query in Oracle databases implemented using CONNECT BY clause and then how to convert using recursive WITH clause.

Hierarchical query using CONNECT BY

Oracle database provides CONNECT BY, START WITH clauses to operate on hierarchical data. LEVEL clause in Oracle database helps you build dynamic hierarchy and also gives you the depth of hierarchy. LEVEL clause is very important consideration to generate “N” number of rows for a certain condition. You can learn in depth of hierarchical query in our documentation here.

Hierarchical query using Recursive Subquery Factoring

The “Recursive Subquery Factoring” is also referred as recursive WITH clause. This functionality was introduced in Oracle 11g Release 2, giving an alternative to the method of CONNECT BY hierarchical querying.

A recursive subquery factoring clause must contain two SELECT query blocks combined by a UNION ALL set operator within a WITH clause.

• The first block is known as the anchor member, which can not reference the query name. It can be made up of one or more query blocks combined by the UNION ALL, UNION, INTERSECT or MINUS set operators.
• The second query block is known as the recursive member, which must reference the query name at-least once.

CONNECT BY hierarchical queries to recursive WITH clause recursive subquery factoring examples in Oracle

Here are some examples of using recursive subquery factoring to understand how they work in Oracle database.

Q : Generate numbers from 1 to 10.

Ans:

SELECT LEVEL AS num
FROM DUAL
CONNECT BY LEVEL <= 10;

WITH cte(n) AS(
--Anchor query part; fixed query; can not reference CTE
SELECT 1 AS n FROM DUAL
UNION ALL
--Recursive part; Must reference CTE at-least once
SELECT n+1 FROM cte
WHERE n+1 <= 10
)
SELECT n AS num FROM cte;

Q : Display last 7 days dates including today.

Ans:

SELECT (sysdate - LEVEL) + 1 AS "Week days"
FROM DUAL
CONNECT BY LEVEL <= 7;

WITH cte(n, days) AS(
SELECT 1, sysdate FROM DUAL
UNION ALL
SELECT n+1, sysdate - n FROM cte
WHERE n+1 <= 7
)
SELECT days AS "Week days" FROM cte;

Correct way of refactoring CONNECT BY queries

Writing queries using recursive WITH clause or converting a hierarchical query written using Oracle native CONNECT BY and START WITH clauses, requires you to understand the hierarchical query execution order in Oracle. Read our Working with Hierarchical query in Oracle database article to master on hierarchical queries in databases.

Let us take an hierarchical query example implemented using CONNECT BY, START WITH clauses and refactor to recursive WITH clause. Example is based on EMP table assuming you have EMP table data as is provided by Oracle database SCOTT schema.

SELECT LEVEL lvl, Empno, Ename, Mgr, Sal
FROM Emp
WHERE Sal > 2500
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;

       LVL	EMPNO ENAME		MGR	   SAL
---------- ---------- ---------- ---------- ----------
	 1	 7839 KING			  5000
	 2	 7566 JONES	       7839	  2975
	 3	 7788 SCOTT	       7566	  3000
	 3	 7902 FORD	       7566	  3000
	 2	 7698 BLAKE	       7839	  2850

WITH ref (lvl, Empno, Ename, Mgr, Sal) AS 
(
    SELECT 1 AS lvl, Empno, Ename, Mgr, Sal
    FROM emp
    WHERE Ename = 'KING'    
    UNION ALL
    SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Sal
    FROM emp, ref
    WHERE ref.empno = emp.mgr
)
SELECT * FROM ref
 WHERE Sal > 2500;

       LVL	EMPNO ENAME		MGR	   SAL
---------- ---------- ---------- ---------- ----------
	 1	 7839 KING			  5000
	 2	 7698 BLAKE	       7839	  2850
	 2	 7566 JONES	       7839	  2975
	 3	 7788 SCOTT	       7566	  3000
	 3	 7902 FORD	       7566	  3000

Refactoring Steps:

1. A recursive subquery factoring using WITH clause, must contain two SELECT query blocks combined by a UNION ALL set operator.
2. Put your START WITH conditions to the 1st SELECT query.
3. Following things must be considered while implementing the 2nd SELECT subquery which is the recursive part.
   • 2nd SELECT query is similar to the CONNECT BY recursion part in a native hierarchical query. It must join the WITH subquery alias at least once (ref in above case).
   • Consider columns from the actual table not from WITH subquery alias (ref in above case).
   • Select columns from WITH subquery alias only if the column is chained which means column is incremented/decremented in case of finding level or depth or if column is combined to other columns/values to find a path.
   • Take column which is followed by PRIOR from WITH subquery alias and the other column from actual table for implementing join. Considering above example, take Empno from WITH subquery alias (which is ref) and Mgr from EMP table. So, your condition "PRIOR Empno = Mgr" is translated as "ref.empno = emp.mgr".
4. Your outer query SELECT should be based on WITH subquery alias.
5. CONNECT BY query WHERE clause must be applied to the end in the recursive WITH query.

We showed above how the START WITH, CONNECT BY constructs are mapped during recursive subquery factoring. But what if the order of steps is not followed and is disturbed.

What happens if filters are applied in the WITH clause subqueries, not to the end?

Let us conclude that with an example and understand the impact when you do not follow the steps in sequence defined.

Scenario 1: Applying filters in subqueries

SELECT LEVEL lvl, Empno, Ename, Mgr, Sal
FROM Emp
WHERE Sal > 2500
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;

       LVL	EMPNO ENAME		MGR	   SAL
---------- ---------- ---------- ---------- ----------
	 1	 7839 KING			  5000
	 2	 7566 JONES	       7839	  2975
	 3	 7788 SCOTT	       7566	  3000
	 3	 7902 FORD	       7566	  3000
	 2	 7698 BLAKE	       7839	  2850

WITH ref (lvl, Empno, Ename, Mgr, Sal) AS 
(
    SELECT 1 AS lvl, Empno, Ename, Mgr, Sal
    FROM emp
    WHERE Ename = 'KING'
      AND Sal > 2500
    UNION ALL
    SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Sal
    FROM emp, ref
    WHERE emp.Sal > 2500
      AND ref.empno = emp.mgr
)
SELECT * FROM ref;

       LVL	EMPNO ENAME		MGR	   SAL
---------- ---------- ---------- ---------- ----------
	 1	 7839 KING			  5000
	 2	 7698 BLAKE	       7839	  2850
	 2	 7566 JONES	       7839	  2975
	 3	 7788 SCOTT	       7566	  3000
	 3	 7902 FORD	       7566	  3000

As you see there is no impact when you moved the filter condition "Sal > 2500" to both the subqueries. However this may not be true in every case. Let us take one more scenario.

Scenario 2: Shows the impact when filter is not applied to the last

SELECT LEVEL lvl, Empno, Ename, Mgr, Job
FROM Emp
WHERE Job = 'MANAGER'
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;

       LVL	EMPNO ENAME		MGR JOB
---------- ---------- ---------- ---------- ---------
	 2	 7566 JONES	       7839 MANAGER
	 2	 7698 BLAKE	       7839 MANAGER
	 2	 7782 CLARK	       7839 MANAGER

WITH ref (lvl, Empno, Ename, Mgr, Job) AS 
(
    SELECT 1 AS lvl, Empno, Ename, Mgr, Job
    FROM emp
    WHERE Ename = 'KING'
      AND Job = 'MANAGER'
    UNION ALL
    SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Job
    FROM emp, ref
    WHERE emp.Job = 'MANAGER'
      AND ref.empno = emp.mgr
)
SELECT * FROM ref;

no rows selected

Isn’t the output strange when you apply filters in the WITH clause subqueries. This abnormality is because, the 1st subquery returns NULL when filter is applied to it and thus the 2nd subquery which is the recursive part has to join at least once with result set returned from 1st query also returns NULL. Having said that, showing below the correct approach of refactoring.

WITH ref (lvl, Empno, Ename, Mgr, Job) AS 
(
    SELECT 1 AS lvl, Empno, Ename, Mgr, Job
    FROM emp
    WHERE Ename = 'KING'    
    UNION ALL
    SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Job
    FROM emp, ref
    WHERE ref.empno = emp.mgr
)
SELECT * FROM ref
 WHERE Job = 'MANAGER';

       LVL	EMPNO ENAME		MGR JOB
---------- ---------- ---------- ---------- ---------
	 2	 7698 BLAKE	       7839 MANAGER
	 2	 7782 CLARK	       7839 MANAGER
	 2	 7566 JONES	       7839 MANAGER

Conclusion

As showed above, you must always keep in mind how CONNECT BY queries are executed in Oracle database. Through this article we explained how to write and understand hierarchical queries in Oracle databases using CONNECT BY and then rewriting using recursive subquery factoring. If you still not confident and have some confusions around, do not be agitated as examples are not limited and we bring you tricky questions and answers on hierarchical queries using CONNECT BY and recursive subquery refactoring with more examples.