Deep or wide?

The recursive with clause (recursive subquery factoring) was introduced in 11.2, this gives an alternative to the good old “start with – connect by prior” syntax used for hierarchical queries. One of the features of this new syntax is that it allows to rather easily traverse the hierarchical relationship in either “direction”.  

Do you want to go deep then wide?

Or do you want to go wide then deep?

To illustrate this here is the classic employee manager relationship in the good old EMP table.  Each employee has a manager, except for the president of the company. 

SQL> select empno,ename,mgr from emp;

EMPNO ENAME        MGR

----- ---------- -----

 7369 SMITH       7902

 7499 ALLEN       7698

 7521 WARD        7698

 7566 JONES       7839

 7654 MARTIN      7698

 7698 BLAKE       7839

 7782 CLARK       7839

 7788 SCOTT       7566

 7839 KING

 7844 TURNER      7698

 7876 ADAMS       7788

 7900 JAMES       7698

 7902 FORD        7566

 7934 MILLER      7782

With the new syntax you can first show the managers and their reports, or show ALL managers then the reports.  Let’s take a look to see what this means.  This first query has the clause SEARCH DEPTH FIRST BY eid.  This gives the output we see below.  

WITH

  org_chart (eid, emp_last, mgr_id, reportLevel) AS

  (

     SELECT empno, ename, mgr,1 reportLevel

     FROM emp

     WHERE job='PRESIDENT'

   UNION ALL

     SELECT e.empno, e.ename, e.mgr,

            reportLevel+1

     FROM org_chart r, emp e

     WHERE r.eid = e.mgr

  )

  SEARCH DEPTH FIRST BY eid SET order1 /* NOTICE THIS LINE */

SELECT lpad(' ',2*reportLevel)||eid emp_no, emp_last

FROM org_chart

ORDER BY order1

/

EMP_NO               EMP_LAST

-------------------- --------

  7839               KING

    7566             JONES

      7788           SCOTT

        7876         ADAMS

      7902           FORD

        7369         SMITH

    7698             BLAKE

      7499           ALLEN

      7521           WARD

      7654           MARTIN

      7844           TURNER

      7900           JAMES

    7782             CLARK

      7934           MILLER

This second query has the clause SEARCH BREADTH FIRST BY eid.  This gives the output we see below.  

WITH

  org_chart (eid, emp_last, mgr_id, reportLevel) AS

  (

     SELECT empno, ename, mgr,1 reportLevel

     FROM emp

     WHERE job='PRESIDENT'

   UNION ALL

     SELECT e.empno, e.ename, e.mgr,

            reportLevel+1

     FROM org_chart r, emp e

     WHERE r.eid = e.mgr

  )

  SEARCH BREADTH FIRST BY eid SET order1 /* NOTICE THIS LINE */

SELECT lpad(' ',2*reportLevel)||eid emp_no, emp_last

FROM org_chart

ORDER BY order1

/

EMP_NO               EMP_LAST

-------------------- --------

  7839               KING

    7566             JONES

    7698             BLAKE

    7782             CLARK

      7499           ALLEN

      7521           WARD

      7654           MARTIN

      7788           SCOTT

      7844           TURNER

      7900           JAMES

      7902           FORD

      7934           MILLER

        7369         SMITH

        7876         ADAMS

With the new syntax you have a bit easier time to negotiate the recursive relationship which every way you like.  Also it does appear to have a performance advantage in that it doesn’t sort the data as much as the old start with syntax.   One last point, the docs don’t seem to say this anywhere but BREADTH FIRST appears to be the default if you don’t put in the clause at all.