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.
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.
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.
e.g.1 - Create a top-down query that displays the management hierarchy of a company.
e.g. 2- Now create a bottom up query that displays a reverse management hierarchy.
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.
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.