The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

giovedì, luglio 21, 2011

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

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

Userò il concetto di query gerarchiche in due casi:
  1. lock
  2. parallel query

Nessun commento: