Hierarchical Queries

Come ottenere un result set utilizzando query gerarichiche.

SELECT LPAD(' ', LEVEL*2)||columnX
FROM table
WHERE condition(s)
START WITH condition(s)
CONNECT BY PRIOR column1 = column2
ORDER SIBLINGS BY columnX;

Ci sono due modi di ricevere i dati (il top-down coincide con la visita in ordine anticipato di un albero):

=======
Top down
=======

column1 = Parent Key
column2 = Child Key

=======
Bottom up
=======

column1 = Child Key
column2 = Parent Key

The direction of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row. To find the child rows of a parent row, the Oracle server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table. Rows for which the condition is true are the child rows of the parent. The Oracle server always selects child rows by evaluating the CONNECT BY condition with respect to a current parent row.

(11g SQL Fundamentals II - App F.7)

You have to be careful when trying to order the rows of output in a hierarchical query. By default, if you omit the ORDER BY clause altogether, the query attempts to sort rows in an order that’s reflective of the hierarchy. Display will start first with a LEVEL 1 row. If that row is superior to any LEVEL 2 rows, those rows will display next before another LEVEL 1 row displays. The same approach is taken at LEVEL 2, so that rows will display down to leaf node levels before the next rows show at the higher levels. The result is a display that is meaningful to the hierarchy. But if you try to order these rows with the ORDER BY clause, you’ll create a syntactically correct statement that probably doesn’t help you much. ORDER SIBLINGS sorts rows within each given level, not across levels, thus retaining the hierarchical relationship across rows of output. Note that our earlier use of ORDER BY did not create an error message, just misleading output

(OCA Oracle Database SQL Certified Expert Exam Guide)

Per esempio, il top-down:

SELECT LPAD(' ', LEVEL*2)||last_name
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

Userò il concetto di query gerarchiche in due casi:

1. lock
2. parallel query