Hierarchical queries

dwarak.k's picture
AttachmentSize
Image icon hierarchical tree.GIF4.52 KB
articles: 

A relational database does not store data in a hierarchical way. Then how do I get the data in a hierarchical manner? Here we get to know about how to use the hierarchical querying feature which Oracle has given. This article talks about how you can interpret the hierarchical query conceptually and build hierarchical queries catering your needs.

Using hierarchical queries, you can retrieve records from a table by their natural relationship. Be it a family tree or a employee/manager tree or what ever.

Tree walking enables you to construct a hierarchical tree if the relationship lie in the same table. For instance, a manager column which exists in the emp table which defines the managerial hierarchy.

We shall take up an example of the emp table in Scott schema. Here King is top most in the hierarchy

empno	ename	job	mgr	hiredate
7369	SMITH	CLERK	7902	17-Dec-80
7499	ALLEN	SALESMAN  7698	20-Feb-81
7521	WARD	SALESMAN  7698	22-Feb-81
7566	JONES	MANAGER	7839	2-Apr-81
7654	MARTIN	SALESMAN  7698	28-Sep-81
7698	BLAKE	MANAGER	7839	1-May-81
7782	CLARK	MANAGER	7839	9-Jun-81
7788	SCOTT	ANALYST	7566	19-Apr-87
7839	KING	PRESIDENT	17-Nov-81
7844	TURNER	SALESMAN  7698	8-Sep-81
7876	ADAMS	CLERK	7788	23-May-87
7900	JAMES	CLERK	7698	3-Dec-81
7902	FORD	ANALYST	7566	3-Dec-81
7934	MILLER	CLERK	7782	23-Jan-82

If we have to query the employees reporting to King directly,

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
WHERE  mgr = 7839

7566	JONES	MANAGER	7839	2-Apr-81
7698	BLAKE	MANAGER	7839	1-May-81
7782	CLARK	MANAGER	7839	9-Jun-81

But if we have to walk down the tree and check who all are reporting to Jones, Blake and Clark (recursively)

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

We will quickly see what are all the key words used in this query.

START WITH – Specifies the root rows of the hierarchy or in other words, where to start parsing from. This clause is necessary for true hierarchical queries

CONNECT BY PRIOR – This explains the relationship between the parent and the child.

PRIOR – This is used to achieve the recursive condition (The actual walking)

Direction of walking the tree

To explain more on the CONNECT BY clause, this is used to determine if you are walking from top to bottom or bottom to top.

CONNECT BY PRIOR col_1 = col_2


If walking from top to bottom

col_1 is the parent Key(One which identifies the parent) and col_2 is the child key (this identifies the child)
And here it is

CONNECT BY PRIOR empno = mgr

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate,
 level 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

Gets me this result

7839	KING      PRESIDENT 	           17-Nov-81	1
7566	JONES     MANAGER   7839	2-Apr-81	2
7788	SCOTT     ANALYST   7566	19-Apr-87	3
7876	ADAMS     CLERK     7788	23-May-87	4
7902	FORD      ANALYST   7566	3-Dec-81	3
7369	SMITH     CLERK     7902	17-Dec-80	4
7698	BLAKE     MANAGER   7839	1-May-81	2
7499	ALLEN     SALESMAN  7698	20-Feb-81	3
7521	WARD      SALESMAN  7698	22-Feb-81	3
7654	MARTIN    SALESMAN  7698	28-Sep-81	3
7844	TURNER    SALESMAN  7698	8-Sep-81	3
7900	JAMES     CLERK     7698	3-Dec-81	3
7782	CLARK     MANAGER   7839	9-Jun-81	2
7934	MILLER    CLERK     7782	23-Jan-82	3

If walking from bottom to top
Col_1 should be the child key and col_2 should be the parent key

CONNECT BY PRIOR mgr = empno

Using Level

LEVEL psedo column shows the level or rank of the particular row in the hierarchical tree. If you see the below query, It shows the level of KING and the level of the guys reporting directly to him

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       LEVEL 
FROM   emp 
WHERE  LEVEL <= 2 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

empno	ename	job	mgr	hiredate	level
7839	KING	PRESIDENT		17-Nov-81	1
7566	JONES	MANAGER	7839	2-Apr-81	2
7698	BLAKE	MANAGER	7839	1-May-81	2
7782	CLARK	MANAGER	7839	9-Jun-81	2

Here The level is used in the where clause to restrict the records till the second level.

Level also can be used to format the Output to form a graph structure

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

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

Pruning branches/children

There might be business requirements to partially retrieve a hierarchical tree and to prune branches.
If you do not want to do so, use the where condition to restrict the branch but process the child row

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
WHERE  ename <> 'JONES' 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

This will restrict the value Jones in the result set but will still will retrieve Scott and Ford.

Please refer to the attached Picture to get a complete understanding.

To Restrict the value clark and its children, you should be adding the condition after the CONNECT BY

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr 
                 AND ename <> 'CLARK'

Comments

It was clear flow of information.Simple example superb explanation.
Thanks a lot

Very informative article. It helps to see data in a structured way.
I frequently use this to view Oracle explain plans. The SQL below (from Oracle documentation) shows how to read explain plans.

SELECT lpad(' ',level-1)||operation||' '||options||' '||
object_name "Plan"
FROM plan_table
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = '&1'
ORDER BY id;

&1 is where you put your statement_id.

manudu20's picture

hi all, can anyone tell me how oracle find this level automatically.

Bye

level is a part of the start with/connect by syntax.
Here is how Oracle comes up with the levels:

The START WITH condition tells Oracle from where to start assigning the level. In this particular example there is only one row that matches the condition where "mgr is NULL" so there is only one row with level 1.
The CONNECT BY PRIOR clause tells Oracle how to build the hierarchy of levels.

Create a table with the above example and play with it.

Hope this helps
Sumit

manudu20's picture

Hi sumit ,
Can u solve one of my problem
I m getting
http://127.0.0.1:2311/l26K6yZtIrDMBfbSeg7Mryj0NWyk3eDHcsH1frlfHxtTVR39
in my address bar while running my forms through form10g.

Can u please help me.

Hi

I am not able to open the link u sent.

Sumit

This is local server's IP address. Please elaborate your question.

The new way to view explain plan information is as follows :

explain plan set statement_id='blahblah' for .....

followed by :

select * from table(dbms_xplan.display);

Hans

Hi manudu20

I am not able to open the link u sent.

Sumit

manudu20's picture

This is not a link dear , this is the url i m getting on address bar of my browser while running forms through form builder.

1. I have installed windows 2003 service pack 1.

2. then I installed oracle 10.0.1.0 .
Created user with dba privilege and import the data.
Now i m able to connect sqlplus through the user i had creted and also able to acess the database.

3. then , I installed Devlopper 10g.
Update tnsnames.ora file for connectvity.

4. created a new form in form builder.
Start the instance.
set the run time prefrences .

compile + run the form .

In a new window Browser is opening but in address bar
http://127.0.0.1:2311/l26K6yZtIrDMBfbSeg7Mryj0NWyk3eDHcsH1frlfHxtTVR39
this link is displaying.

My system ip is 59.165.13.168 and port is 1521 but in form runtime this ip and port is coming.
I dont know whats the problem is?

can u help me.

manudu20's picture

Hi,
As it seems your Oracle Forms runtime is picking the LOCAL IP ADDRESS of your system.

Check the edit>>preferences>>runtime setting of your forms.

Auditor wants all in one line from PO till Cheque payment

SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
G.UNIT_PRICE "UNIT PRICE",
(NVL(C.QUANTITY_ORDERED,0)-
NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO STATUS",
A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
A.INVOICE_NUM "INVOICE NUMBER",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID =
B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER "CHEQUE NUMBER",
TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
AND D.TYPE_LOOKUP_CODE != 'BLANKET';
Display should be in 4 parts
1. Information for Supplier
2. Purchase Order details
3. Receiving Items Details
4. Invoice Details
5. Payment Details

Thanks.

Kasi, Vijayakumar

Do we have any other way to retrieve hierarchical queries without using connect by prior.

Hi, Dwarak.k,

How did you generate the "hierarchical tree.GIF" picture? I know SQL*Plus, SQL developer, Toad only. What tools do I need to visualize the queried hierarchical data in tree format?

Thanks.

zhx

How many levels will this allow you to go?