Hierarchical queries

Hierarchical queries

Hierarchical query is a query which retrieves the data from database based on hierarchical structure in the table. Take employees table as an example:

Check the hierarchies in the employees table:

Select empno, ename from EMP where mgr is null;

EMPNO ENAME

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

7839 KING

So king is in the top level of the hierarchy. So oracle assigns level as 1

Select empno, ename from EMP where mgr=7839;

EMPNO ENAME

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

7698 BLAKE

7782 CLARK

7566 JONES

Above employees are reporting to king directly. So they are in level 2.

Select EMPNO, ENAME FROM EMP WHERE MGR=7698

EMPNO ENAME

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

7654 MARTIN

7499 ALLEN

7844 TURNER

7900 JAMES

7521 WARD

Select EMPNO, ENAME FROM EMP WHERE MGR=7782;

EMPNO ENAME

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

7934 MILLER

Select EMPNO, ENAME FROM EMP WHERE MGR=7566;

EMPNO ENAME

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

7902 FORD

7788 SCOTT

So above all employees on level 3.

Select empno, ename from EMP where mgr=7902

EMPNO ENAME

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

7369 SMITH

Select empno, ename from EMP where mgr=7788

EMPNO ENAME

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

7876 ADAMS

So above employees on level 4.

If I want to know the employees who is working under Jones then query will be like this

Select ename, empno from EMP

Where mgr = (select empno from EMP where ename =’Jones’);

To know employees who are working under the employees who are working under Jones. Query will be like this:

Select ename, empno from EMP

Where mgr in (select empno from EMP

Where mgr= (select empno from EMP where ename =’Jones’));

So if we want to read the data from deeper level our query will be increasing. To handle this type of situations oracle have the hierarchical concept.

Hierarchical query:

Select * from EMP

Connect by prior condition

Start with node

Connect by clause:

It is a clause which specifies the parent and child relation condition.

Ex: connect by empno=mgr;

Prior is an operator which specifies whether we require the parents or Childs.

Ex: connect by prior empno =mgr

In the above example prior is specified before empno so query retrieves all the employees working under manager specified in start with clause.

Ex: connect by empno = prior mgr

Above example prior is specified by before mgr so query retrieves all the managers to whom employee is related.

Start with clause:

Start with clause directs the oracle which node should be took as a parent. If we did not specify the start node then oracle treat every node as a parent node gives the output starting from parent or child based on prior operator. Using start with we can know who are the employees working under specific manager or specific employee corresponding top level managers.

Example:

1. Start with mgr is null.

2. Start with empno=7788

3. Start with ename=’JONES’

Examples of hierarchical query’s:

1. all the employees from top level wise.

SELECT LPAD (' ', 2 * (LEVEL - 1)) || ename "employee" FROM emp

START WITH mgr IS NULL

CONNECT BY PRIOR empno = mgr;

employee

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

KING

BLAKE

MARTIN

ALLEN

TURNER

JAMES

WARD

CLARK

MILLER

JONES

FORD

SMITH

SCOTT

ADAMS

2. All the employees working under smith;

Select empno, ename, mgr from EMP

Start with ename='JONES'

CONNECT BY PRIOR EMPNO=MGR

EMPNO ENAME MGR

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

7566 JONES 7839

7902 FORD 7566

7369 SMITH 7902

7788 SCOTT 7566

7876 ADAMS 7788

3. All the managers under whom Jones working.

Select empno, ename, mgr from EMP

Start with ename=’JONES’

CONNECT BY empno= prior mgr;

EMPNO ENAME MGR

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

7566 JONES 7839

7839 KING

Leaf node : Leaf node is a node for which there is no child.

Parent node : node having at least one child

Child node : node having parent node.

Root node : node having no parent node.

LEVEL:

It is a pseudo column which gives the level of the current row. Level is 1 for root row, 2 for all his child rows, 3 for all his grand Childs etc.

Ex:

1. Select LEVEL,EMPNO,ENAME,MGR FROM EMP

START WITH EMPNO=7788

CONNECT BY EMPNO= PRIOR MGR;

LEVEL EMPNO ENAME MGR

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

1 7788 SCOTT 7566

2 7566 JONES 7839

3 7839 KING

2. Select LEVEL, EMPNO, ENAME, MGR FROM EMP

START WITH EMPNO=7839

CONNECT BY PRIOR EMPNO= MGR;

LEVEL EMPNO ENAME MGR

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

1 7839 KING

2 7698 BLAKE 7839

3 7654 MARTIN 7698

3 7499 ALLEN 7698

3 7844 TURNER 7698

3 7900 JAMES 7698

3 7521 WARD 7698

2 7782 CLARK 7839

3 7934 MILLER 7782

2 7566 JONES 7839

3 7902 FORD 7566

4 7369 SMITH 7902

3 7788 SCOTT 7566

4 7876 ADAMS 7788

CONNECT_BY_ISLEAF :

It is a pseudo column which gives information about the node that is whether this node is leaf node or not. If it is leaf node it will give 1 other wise 0.

Ex:

SELECT ename, connect_by_isleaf FROM EMP

START WITH ENAME ='KING'

CONNECT BY PRIOR EMPNO= MGR;

ENAME CONNECT_BY_ISLEAF

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

KING 0

BLAKE 0

MARTIN 1

ALLEN 1

TURNER 1

JAMES 1

WARD 1

CLARK 0

MILLER 1

JONES 0

FORD 0

SMITH 1

SCOTT 0

ADAMS 1

SELECT ename, connect_by_isleaf FROM EMP

START WITH mgr=7788

CONNECT BY EMPNO= prior MGR;

ENAME CONNECT_BY_ISLEAF

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

ADAMS 0

SCOTT 0

JONES 0

KING 1

CONNECT_BY_ISCYCLE:

The connect_by_iscycle is a pseudo column return 1 if the current row has a child which is also its parent. Otherwise it returns 0. Prerequisite is we have to use connect by with nocycle option.

Ex:

1. SELECT ename, connect_by_iscycle from emp

START WITH mgr=7788

CONNECT BY nocycle EMPNO= prior MGR;

ENAME CONNECT_BY_ISCYCLE

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

ADAMS 0

SCOTT 0

JONES 0

KING 0

2. I am creating the loop by updating the king manager number to adams employee Number.

UPDATE EMP

SET MGR=7788

WHERE EMPNO=7839;

SELECT empno, connect_by_iscycle FROM EMP

CONNECT BY NOCYCLE EMPNO=PRIOR MGR

START WITH EMPNO=7788

EMPNO CONNECT_BY_ISCYCLE

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

7788 0

7566 0

7839 1

SQL> SELECT EMPNO, ENAME, MGR FROM EMP

2 CONNECT BY EMPNO=PRIOR MGR

3 START WITH EMPNO=7788;

ERROR:

ORA-01436: CONNECT BY loop in user data.

SELECT EMPNO, ENAME, MGR FROM EMP

CONNECT BY NOCYCLE EMPNO=PRIOR MGR

START WITH EMPNO=7788

EMPNO ENAME MGR

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

7788 SCOTT 7566

7566 JONES 7839

7839 KING 7788

SYS_CONNECT_BY_PATH:

It returns the path of the current node from root node.

Systax:

               Sys_connect_by_path (column, char)

Ex:

Select sys_connect_by_path (ename,'/') path from EMP

Start with empno=7788

Connect by empno= prior mgr;

                       PATH

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

                 /SCOTT

                 /SCOTT/JONES

                 /SCOTT/JONES/KING

Sorting in hierarchical queries:

If we did the ordering using the standard order by clause output of the query will lost his hierarchical structure. So oracle has new order by clause which orders the each level without destroying the hierarchical structure.

Ex; Order siblings by column_name

Before ordering:

Ex: select level, empno, ename from EMP

Connect by prior empno=mgr

Start with mgr is null;

LEVEL EMPNO ENAME

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

1 7839 KING

2 7698 BLAKE

3 7654 MARTIN

3 7499 ALLEN

3 7844 TURNER

3 7900 JAMES

3 7521 WARD

2 7782 CLARK

3 7934 MILLER

2 7566 JONES

3 7902 FORD

4 7369 SMITH

3 7788 SCOTT

4 7876 ADAMS

After ordering:

Select level, empno, ename from EMP

Connect by prior empno=mgr

Start with mgr is null

Order siblings by ename:

LEVEL EMPNO ENAME

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

1 7839 KING

2 7698 BLAKE

3 7499 ALLEN

3 7900 JAMES

3 7654 MARTIN

3 7844 TURNER

3 7521 WARD

2 7782 CLARK

3 7934 MILLER

2 7566 JONES

3 7902 FORD

4 7369 SMITH

3 7788 SCOTT

4 7876 ADAMS