It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Hierarchical Query in Oracle 10g database

A Hierarchical query is a method of reporting the branches of a tree in a specific order. You use hierarchical queries to retrieve data based on a natural hierarchical relationship between rows a table. 

Data is not stored in a hierarchical way. However a process called tree walking allows you to construct the hierarchy.
A Hierarchical query is possible when a relationship exists between rows in a single table.
e.g. MANAGER_ID column of employees table: Employee_id, Last_Name, Job_Id, Manager_ID

Syntax of hierarchical query:
SELECT [LEVEL], column
FROM table
[WHERE condition]
[START WITH condition]
[CONNECT BY PRIOR condition]

SELECT: statement cannot contain a join or query from a view that contains a join.

LEVEL: For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row 2 for a child of root and so on.
When used with the SELECT and CONNECT BY statement, the LEVEL pseudocolumn organizes rows from a database table into a tree structure. Oracle 

FROM: specifies the table, view or snapshot containt the columns. You can select from only one table.

START WITH: specifies the root rows of the hierarchy where the query starts. START WITH can specify any valid condition and can contain subqueries. If a START WITH condition return multiple rows, Oracle generates multiple hierarchies.

CONNECT BY: specifies the columns that contain related parent and child PRIOR rows. Using the CONNECT BY statement, you can create queries that report that branches of a tree from top to bottom or from bottom to top.
Once oracle has identified the parent rows, it uses uses the condition in CONNECT BY clause to select immediate child rows of each parent.

Then oracle select the child rows of those child rows and return seccessive generation of child rows untill the query is complete.

In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent rows.

The direction of the query depends on where you place the PRIOR operator, which refer to parent rows. These two PRIOR clause are equivalent:
PRIOR exp1 = exp2 and exp2 = PRIOR exp1

e.g.1 - Create a top-down query that displays the management hierarchy of a company. 
SELECT last_name, job_id, employee_id, manager_id
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

last_name job_id employee_id manager_id
King AD_PRESS 100
Herry MK_MAN 201 100
John MK_REP 202 201

e.g. 2- Now create a bottom up query that displays a reverse management hierarchy. 
SELECT last_name, job_id, employee_id, manager_id
FROM employees
START WITH employee_id = 105
CONNECT BY PRIOR manager_id = employee_id;

last_name job_id employee_id manager_id
Austin IT_PROG 105 103
Hunold IT_PROG 103 102
De Han AD_VP 102 100
King AD_PRESS 100

Making readable hierachy:
Suppose you want to make the hierarchy of the tree easy to read. You use the LPAD function with LEVEL to indent child rows with underscore character.
SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL*2)-2,'-') AS employee, job_id
FROM employees 
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

Result is that the lower employees are in the hierarchy, the further to the right their names display.

e.g. 3: Create a query that returns all rows with FI_ACCOUNT in the JOB_ID column and all child rows with Popp in the last_name.
SELECT last_name, job_id, employee_id, manager_id
FROM employees
WHERE job_id != 'FI_ACCOUNT'
CONNECT BY PRIOR employee_id = manager_id
AND  last_name != 'Popp';

Use of Hierarchy queries: 


You Might Also Like

Related Posts with Thumbnails