Hierarchical Data

CONNECT_BY_ROOT

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11735.html]SQL Snippets: Hierarchical Data - CONNECT_BY_ROOT[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11735.html">SQL Snippets: Hierarchical Data - CONNECT_BY_ROOT</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Hierarchical Data - CONNECT_BY_ROOT
  • URL (href): http://www.sqlsnippets.com/en/topic-11735.html

Revision Notes

Date Category Note
2007-06-07 Revision Added columns to some examples for clarity.
2007-07-04 Revision Moved sections on CONNECT_BY_ISLEAF and SYS_CONNECT_BY_PATH to separate topics.