Say we wanted to take the following directory names from a file system and store them in a database table.
/nls /nls/demo /nls/mesg /server /server/bin /server/config /server/config/log /server/ctx /server/ctx/admin /server/ctx/data /server/ctx/data/delx /server/ctx/data/enlx /server/ctx/data/eslx /server/ctx/mig
To do this we could use a table with a KEY column, which holds the directory name, and a PARENT_KEY column, which connects the directory to its parent directory. (Directory names like these would not typically be used as primary keys. We are bending the rules here for illustrative purposes.)
select * from t ;
KEY PARENT_KEY NAME ---------- ---------- ---------- nls (null) NLS demo nls DATA mesg nls DEMO server (null) SERVER bin server BIN config server CONFIG log config LOG ctx server CTX admin ctx ADMIN data ctx DATA delx data DELX enlx data ENLX eslx data ESLX mig ctx MESG
To connect and order the data in this table using the PARENT_KEY hierarchy we can create a Hierarchical Query using the START WITH and CONNECT BY clauses of the SELECT command. START WITH identifies the topmost rows in the hierarchy. CONNECT BY identifies all subsequent rows in the hierarchy.
The following snippet returns rows sorted hierarchically, starting from the root rows (those with no parents) on down through to the leaf rows (those with no children).
select key , level from t START WITH parent_key is null CONNECT BY parent_key = prior key ;
KEY LEVEL ---------- ------ nls 1 demo 2 mesg 2 server 1 bin 2 config 2 log 3 ctx 2 admin 3 data 3 delx 4 enlx 4 eslx 4 mig 3
The LEVEL pseudocolumn in the previous result indicates which level in the hierarchy each row is at. The topmost level is assigned a LEVEL of 1. To better illustrate hierarchical relationships the LEVEL column is commonly used to indent selected values, like this.
select lpad( ' ', level-1 ) || key as key_indented , level from t START WITH parent_key is null CONNECT BY parent_key = prior key ;
KEY_INDENTED LEVEL --------------- ------ nls 1 demo 2 mesg 2 server 1 bin 2 config 2 log 3 ctx 2 admin 3 data 3 delx 4 enlx 4 eslx 4 mig 3
The PRIOR operator in hierarchical queries gives us access to column information from the parent of the current row. It can be used outside the CONNECT BY clause if required.
select lpad( ' ', level-1 ) || key as key_indented , PRIOR key as prior_key , PRIOR name as prior_name from t start with parent_key is null connect by parent_key = prior key ;
KEY_INDENTED PRIOR_KEY PRIOR_NAME --------------- ---------- ---------- nls (null) (null) demo nls NLS mesg nls NLS server (null) (null) bin server SERVER config server SERVER log config CONFIG ctx server SERVER admin ctx CTX data ctx CTX delx data DATA enlx data DATA eslx data DATA mig ctx CTX
Changing Direction
To traverse the tree in the opposite direction, from leaf to root, simply choose a leaf row as the starting point and apply the PRIOR operator to the PARENT_KEY column instead of the KEY column.
select lpad( ' ', level-1 ) || key as key_indented , level from t START WITH KEY = 'delx' connect by key = PRIOR PARENT_KEY ;
KEY_INDENTED LEVEL --------------- ------ delx 1 data 2 ctx 3 server 4
Gotchas
CONNECT BY conditions are not applied to rows in level 1 of the hierarchy.
In the following snippet note how the KEY <> 'delx' condition did not
filter out the row with a KEY value of 'delx'.
select lpad( ' ', level-1 ) || key as key_indented , level from t start with key = 'delx' connect by key = PRIOR PARENT_KEY and KEY <> 'delx' ;
KEY_INDENTED LEVEL --------------- ------ delx 1 data 2 ctx 3 server 4