Oracle Hierarchical Query Operators

Description

A hierarchical query is a type of SQL query that handles hierarchical model data. There are a special case of more general recursive fixpoint queries, which compute transitive closures. In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions.

An alternative syntax is the non-standard CONNECT BY construct; it was introduced by Oracle in the 1980s. Prior to Oracle 10g, the construct was only useful for traversing acyclic graphs because it returned an error on detecting any cycles.

In Oracle PRIOR and CONNECT_BY_ROOT, operators are valid only in hierarchical queries.

PRIOR Operator :

• 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.

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 a run time and returns an error.

CONNECT_BY_ROOT Operator:

• CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries.
• When we 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: You cannot specify this operator in the START WITH condition or the CONNECT BY condition.

Hierarchical Query Examples

The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:

Sample table: employees

SELECT employee_id, first_name, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

Sample Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 MANAGER_ID
----------- -------------------- ------------------------- ----------
        101 Neena                Kochhar                          100
	108 Nancy                Greenberg                        101
	109 Daniel               Faviet                           108
	113 Luis                 Popp                             108
	112 Jose Manuel          Urman                            108
	111 Ismael               Sciarra                          108
	110 John                 Chen                             108
	205 Shelley              Higgins                          101
        206 William              Gietz                            205
	...............

LEVEL Example:

The following example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:

Sample table: employees

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

Output

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        113 Popp                             108          3
        112 Urman                            108          3
        111 Sciarra                          108          3
        110 Chen                             108          3
        205 Higgins                          101          2
        206 Gietz                            205          3
        204 Baer                             101          2
        203 Mavris                           101          2
        200 Whalen                           101          2
        201 Hartstein                        100          1
        202 Fay                              201          2 
	...............

Note: In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will override the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.

Previous: Arithmetic Operators
Next: Set Operators