Hierarchical Queries

From my childhood days I always wanted to make sure that I kept things simple . My affair with Oracle started from my high school days but there was one thing that kept me away all the time , "Hierarchical Queries " . It took around 8 long years for me to take a look at it again , of course when one of my friends was taking a look at the production query which was simply taking a lot of time to execute .

Lets take the look at the tree (hierarchical) image in the right . Suppose my table has been designed in such a way and I would like to display my records in the following manner . Then we would have to go for Hierarchical Queries.

• KING PRESIDENT
  • BLAKE MANAGER
     • ALLEN SALESMAN
     • JAMES CLERK
     • MARTIN SALESMAN
     • TURNER SALESMAN
     • WARD SALESMAN
  • CLARK MANAGER
     • MILLER CLERK
  • JONES MANAGER
     • FORD ANALYST
         • SMITH CLERK
     • SCOTT ANALYST
         • ADAMS CLERK

START WITH specifies the root row(s) of the hierarchy.

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. PRIOR is most commonly used when comparing column values with the equality operator.

say :

SELECT ENAME, JOB, EMPNO, MGR, LEVEL
           FROM EMP
     CONNECT BY MGR = PRIOR EMPNO
     START WITH MGR IS NULL
ORDER SIBLINGS BY ENAME;