Thursday, October 13, 2016

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.