Hierarchical Queries in Oracle SQL Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Hierarchical Queries

A hierarchy is built upon a parent-child relationship within the same table or view. Hierarchical query is one of the very first features of the Oracle Database and was introduced more than twenty years ago!

In a traditional query, Oracle looks for a good execution planand retrieves the rows one after the other, in no specific order. In a hierarchy, the rows are organized as a tree:

In the well known employee (EMP) table, King is the president. Below the president, each manager has his own department. In the accounting department, Clarke has one clerk, Miller. The sales department is under the control of Blake. Blake has four salesmen - Allen, Ward, Martin and Turner - and one clerk, James. Jones leads the research department. The analyst Ford has one clerk named Smith and the analyst Scott also has one clerk, Adams.

Most companies have a very hierarchical structure. The hierarchy can be traversed bottom-up; for example, to retrieve the top hierarchy of an employee, or top-down, to discover all employees under one manager.

One of the most common issues in hierarchies is the cycling. That is, if the top manager is his own boss, Oracle will detect a loop and return an error. This issue is partly fixed in 10g by a new mechanism which sets a flag and stops processing the offending branch.

Until 9i, the fields available were either on the current row or on the parent row. In 10g, the fields at the top of the hierarchy are also accessible, which enables analysis and aggregation over the whole hierarchy. One more addition in 10g is the flag for the bottom of the hierarchy, called the leaf rows.

CONNECT BY, PRIOR and START WITH

There are two mandatory keywords to build a hierarchy, CONNECT BYand PRIOR. A hierarchy is built when one row is the parent of another row. START WITHdefines the first ancestor.

SELECT
   ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH

Jones and his employees are returned. Adams is an employee of Scott and Scott is an employee of Jones so Adams is also returned. 

LEVEL

The pseudo-column level returns the depth of the hierarchy. The first level is the root:

SELECT
   ENAME
FROM
   EMP
WHERE
   LEVEL=2
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

ENAME
--------
SCOTT
FORD

Only the direct employees of Jones are returned. Jones is the first ancestor and has a level of 1. Adams and Smith are one level below the direct employees and belong to the third level started by Jones.

The tree is displayed with the children indented under their parents by using padding with a number of spaces proportional to LEVEL.

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL;

ENAME
------------------
KING
   JONES
      SCOTT
         ADAMS
      FORD
         SMITH
   BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
   CLARK
      MILLER

Starting with the top manager, the names of the employees are padded with white spaces according to their level.

ORDER SIBLINGS BY

The rows in a hierarchical query are returned as a tree, the children following the parent. ORDER SIBLINGS BYpreserves the hierarchy and orders the children of each parent.

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL
ORDER SIBLINGS BY
   EMP.ENAME;

ENAME
----------------
KING
   BLAKE
      ALLEN
      JAMES
      MARTIN
      TURNER
      WARD
   CLARK
      MILLER
   JONES
      FORD
         SMITH
      SCOTT
         ADAMS

Clark comes after Blake and before Jones; they are under King and ordered by their name. Their children are sorted and the hierarchical appearance is preserved.

ORDER BYwithout SIBLINGSdestroys the hierarchy:

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL
ORDER BY
   EMP.ENAME;

ENAME
--------------
         ADAMS
      ALLEN
   BLAKE
   CLARK
      FORD
      JAMES
   JONES
KING
      MARTIN
      MILLER
      SCOTT
         SMITH
      TURNER
      WARD

The names are sorted.