The CONNECT_BY_ROOT operator returns column information from the root row of the hierarchy.
select
lpad(' ', level-1 ) || key as key_indented ,
level ,
key ,
name ,
CONNECT_BY_ROOT key as root_key ,
CONNECT_BY_ROOT name as root_name
from
t
start with
parent_key is null
connect by
parent_key = prior key
;
KEY_INDENTED LEVEL KEY NAME ROOT_KEY ROOT_NAME --------------- ------ ---------- ---------- ---------- ---------- nls 1 nls NLS nls NLS demo 2 demo DATA nls NLS mesg 2 mesg DEMO nls NLS server 1 server SERVER server SERVER bin 2 bin BIN server SERVER config 2 config CONFIG server SERVER log 3 log LOG server SERVER ctx 2 ctx CTX server SERVER admin 3 admin ADMIN server SERVER data 3 data DATA server SERVER delx 4 delx DELX server SERVER enlx 4 enlx ENLX server SERVER eslx 4 eslx ESLX server SERVER mig 3 mig MESG server SERVER
Gotchas
The manual page for CONNECT_BY_ROOT states
You cannot specify this operator in the START WITH condition or the CONNECT BY condition.
While there would be little use for CONNECT_BY_ROOT in the START WITH condition, which already operates on the root row itself, using CONNECT_BY_ROOT in the CONNECT BY condition can be useful and, in practice, actually works in some cases (as tested in Oracle 10g). In the following example we use CONNECT_BY_ROOT in the CONNECT BY condition to prevent any rows beyond level 3 under only the "server" root row from being included in the results.
select
lpad(' ', level-1 ) || key as key_indented ,
level ,
CONNECT_BY_ROOT key as root_key
from
t
start with
parent_key is null
connect by
parent_key = prior key and
not ( level > 3 and connect_by_root key = 'server' )
;
KEY_INDENTED LEVEL ROOT_KEY --------------- ------ ---------- nls 1 nls demo 2 nls mesg 2 nls server 1 server bin 2 server config 2 server log 3 server ctx 2 server admin 3 server data 3 server mig 3 server
The fact that the query above contradicts the documentation yet works without error in 10g suggests a bug in either the documentation or the SQL engine.
The Gotchas section of topic CONNECT BY LEVEL Method has an example where using CONNECT_BY_ROOT in CONNECT BY does not work so well.