Hierarchical Queries

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:

START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

• The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop
• In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
• ... PRIOR expr = expr
• or
• ... expr = PRIOR expr
•

If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. For example:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...


PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.
Oracle processes hierarchical queries as follows:

• A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
• The CONNECT BY condition is evaluated.
• Any remaining WHERE clause predicates are evaluated.

Oracle then uses the information from these evaluations to form the hierarchy using the following steps:
1. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.
2. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.
3. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
4. If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause.

Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

In hierarchical queries the syntax is as follows,
SELECT EMP.*,sys_connect_by_path(ename,'/'),LEVEL,connect_by_iscycle,connect_by_isleaf,CONNECT_BY_ROOT ename
FROM EMP
CONNECT BY NOCYCLE PRIOR empno=mgr
ORDER siblings BY ename


sys_connect_by_path(ename,'/'):
SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.
Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.

connect_by_iscycle:
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of aCONNECT BY loop in the data.

connect_by_isleaf:
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.

Level:
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children

Prior:
In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error. Please refer to “Hierarchical Queries” for more information on this operator, including examples.

connect_by_root:
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.
Restriction on CONNECT_BY_ROOT You cannot specify this operator in the START WITH condition or the CONNECT BY condition

siblings:
The SIBLINGS keyword is valid only if you also specify the hierarchical_query_clause (CONNECT BY). ORDER SIBLINGS BY preserves any ordering specified in the hierarchical query clause and then applies theorder_by_clause to the siblings of the hierarchy

Note:
1. In hierarchical queries where clause (not joins) is executed after the connect by clause.
2. Level can be used in the where clause of hierarchical query.
3. Where cant be placed after the connect by clause.

Here is a sample query which includes all the keywords

select empno, sys_connect_by_path(ename,'/') path, connect_by_root(ename), connect_by_isleaf, connect_by_iscycle
from emp
start with mgr is null
connect by nocycle prior empno = mgr
order siblings by ename
/

The simple implementation of hierarchical query is as followed by,
ORA11GR1> select * from counter where mgr=0;

X Y MGR
---------- ---------- ----------
1 A 0


It then applies the connect by TO EACH RECORD RETURNED BY THE START WITH. So, once this start with record is "output", it becomes the "prior" record. So, we take "X" from this prior record
ORA11GR1> select x from counter where mgr = 0;

X
----------
1



and use that value to retrieve the next level of records by MGR:
ORA11GR1> select * from counter where (select x from counter where mgr=0) = mgr;

X Y MGR
---------- ---------- ----------
2 B 1
3 C 1


so, that start with record is connected to these two records. Now, they each in turn become the "prior" record, so we'll take X=2 to find any MGR=2 records and output them (whence they themselves become the prior record and so on)

resulting in the hierarchy:
ORA11GR1> SELECT rpad('*',2*level,'*') data, x,y,mgr,LEVEL
2 FROM (SELECT x,y,mgr FROM counter )
3 START WITH mgr=0 CONNECT BY PRIOR x=mgr
4 ORDER BY mgr
5 /

DATA X Y MGR LEVEL
---------- ---------- ---------- ---------- ----------
** 1 A 0 1
**** 2 B 1 2
**** 3 C 1 2
****** 4 D 2 3
****** 6 F 2 3
****** 5 E 3 3
******** 7 G 4 4
******** 8 H 6 4

8 rows selected.


one level=1 (start with)
connected to the two records such that their MGR = level 1's X=1 value.


If you leave off the start with, that is like saying "start with 1=1, EVERY record". So every record is a level one record (and maybe someone elses level 2, 3, 4, whatever....)
ORA11GR1> SELECT rpad('*',2*level,'*') data, x,y,mgr,LEVEL
2 FROM (SELECT x,y,mgr FROM counter )
3 START WITH 1=1 CONNECT BY PRIOR x=mgr
4 ORDER BY mgr
5 /

DATA X Y MGR LEVEL
---------- ---------- ---------- ---------- ----------
** 1 A 0 1
**** 2 B 1 2
**** 3 C 1 2
** 2 B 1 1
** 3 C 1 1
**** 4 D 2 2
****** 6 F 2 3
** 6 F 2 1
**** 6 F 2 2
** 4 D 2 1
****** 4 D 2 3
****** 5 E 3 3
**** 5 E 3 2
** 5 E 3 1
******** 7 G 4 4
****** 7 G 4 3
** 7 G 4 1
**** 7 G 4 2
**** 8 H 6 2
** 8 H 6 1
******** 8 H 6 4
****** 8 H 6 3

22 rows selected.