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'

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id

AND  last_name != 'Popp';

Use of Hierarchy queries: