Understand Oracle 10g's new Hierarchical Query options

Oracle 10g adds new pseudo columns to the SQL Hierarchical Queries that use START WITH . . . CONNECT BY . . . clauses. For more than a decade, Oracle SQL has had the ability to cause a query to follow a hierarchical relationship. For instance, you could specify a starting condition and continue to child rows using one or more connection conditions. As an example, suppose I have a table that lists some regions of the world.

For instance, you could specify a starting condition and continue to child rows using one or more connection conditions. As an example, suppose I have a table that lists some regions of the world.

create table hier
(
    parent  varchar2(30),
    child   varchar2(30)
);
insert into hier values(null,'Asia');
insert into hier values(null,'Australia');
insert into hier values(null,'Europe');
insert into hier values(null,'North America');
insert into hier values('Asia','China');
insert into hier values('Asia','Japan');
insert into hier values('Australia','New South Wales');
insert into hier values('New South Wales','Sydney');
insert into hier values('California','Redwood Shores');
insert into hier values('Canada','Ontario');
insert into hier values('China','Beijing');
insert into hier values('England','London');
insert into hier values('Europe','United Kingdom');
insert into hier values('Japan','Osaka');
insert into hier values('Japan','Tokyo');
insert into hier values('North America','Canada');
insert into hier values('North America','USA');
insert into hier values('Ontario','Ottawa');
insert into hier values('Ontario','Toronto');
insert into hier values('USA','California');
insert into hier values('United Kingdom','England');

I can connect a parent region to a child region using the START WITH . . . CONNECT BY . . . clause and display the level of the hierarchy.

column child format a40
select level,lpad(' ',level*3)||child child
  from hier
 start with parent is null
 connect by prior child = parent;
LEVEL    CHILD
----------   --------------------------
         1    Asia
         2       China
         3          Beijing
         2       Japan
         3          Osaka
         3          Tokyo
         1    Australia
         2       New South Wales
         3          Sydney
         1    Europe
         2       United Kingdom
         3          England
         4             London
         1    North America
         2       Canada
         3          Ontario
         4             Ottawa
         4             Toronto
         2       USA
         3          California
         4             Redwood Shores

Since Oracle 9i, the SYS_CONNECT_BY_PATH function has allowed you to a "path" or list of hierarchical elements that led to the current point.

column path format a50
select level,sys_connect_by_path(child,'/') path
  from hier
 start with parent is null
 connect by prior child = parent;
LEVEL      PATH
--------      --------------------------------------------
    1           /Asia
    2          /Asia/China
    3          /Asia/China/Beijing
    2          /Asia/Japan
    3          /Asia/Japan/Osaka
    3          /Asia/Japan/Tokyo
    1          /Australia
    2          /Australia/New South Wales
    3          /Australia/New South Wales/Sydney
    1          /Europe
    2          /Europe/United Kingdom
    3          /Europe/United Kingdom/England
    4          /Europe/United Kingdom/England/London
    1          /North America
    2          /North America/Canada
    3          /North America/Canada/Ontario
    4          /North America/Canada/Ontario/Ottawa
    4          /North America/Canada/Ontario/Toronto
    2          /North America/USA
    3          /North America/USA/California
    4          /North America/USA/California/Redwood Shores

In Oracle 10g, there are some more new features to help with Hierarchical Queries. Sometimes, for example, you may only be interested in the lowest levels of each branch or "leaves." Now you can determine whether the current row is a leaf with the new pseudocolumn CONNECT_BY_ISLEAF. It will contain "1" if the row is a leaf or "0" if it is a branch (i.e., it is a parent to other rows).

select connect_by_isleaf,sys_connect_by_path(child,'/') path
  from hier
 start with parent is null
 connect by prior child = parent;
CONNECT_BY_ISLEAF     PATH
----------------------------------   ------------
                0                               /Asia
                0                               /Asia/China
                1                               /Asia/China/Beijing
            ! ;    0                               /Asia/Japan
                1                               /Asia/Japan/Osaka
                1                               /Asia/Japan/Tokyo
                0         &nb! sp;         &nbs! p;           /Australia
                0                               /Australia/New South Wales
                1                               /Australia/New South Wales/Sydney
                0                               /Europe
                0                               /Europe/United Kingdom
                0                               /Europe/United Kingdom/England
                1                               /Europe/United Kingdom/England/London
                0                               /North America
                0                               /North America/Canada
                0                               /North America/Canada/Ontario
                1                               /North America/Canada/Ontario/Ottawa
                1                               /North America/Canada/Ontario/Toronto
                0                               /North America/USA
                0                               /North America/USA/California
                1                               /North America/USA/California/Redwood Shores

There is also a new operator, CONNECT_BY_ROOT. You use it before a column name to return the value of the root node in the current hierarchy. For example, I can show the top level node in my hierarchy table along with the current row data.

select connect_by_root child,sys_connect_by_path(child,'/') path
  from hier
 start with parent is null
connect by prior child = parent;
CONNECT_BY_ROOT    PATH
------------------------------    --------
Asia                                  /Asia
Asia                                  /Asia/China
Asia                                  /Asia/China/Beijing
Asia                                  /Asia/Japan
Asia                                  /Asia/Japan/Osaka
Asia                                  /Asia/Japan/Tokyo
Australia                         /Australia
Australia                         /Australia/New South Wales
Australia                         /Australia/New South Wales/Sydney
Europe                             /Europe
Europe                             /Europe/United Kingdom
Europe                             /Europe/United Kingdom/England
Europe                             /Europe/United Kingdom/England/London
North America               /North America
North America               /North America/Canada
North America               /North America/Canada/Ontario
North America               /North America/Canada/Ontario/Ottawa
North America               /North America/Canada/Ontario/Toronto
North America               /North America/USA
North America               /North America/USA/California
North America               /North America/USA/California/Redwood Shores

Before Oracle 10g, if you had a circular loop in your tree (one where a child referenced a parent), Oracle would simply return an error, " ORA-01436: CONNECT BY loop in user data". There was no way to issue the query without removing the row referencing a parent. In Oracle 10g, you can specify "NOCYCLE" to query the data anyway. In conjunction with this new keyword, there is another pseudocolumn, CONNECT_BY_ISCYCLE, which will evaluate to "1" if the current row references a parent and would create a loop in the tree.

create table hier2
(
    parent  number,
    child   number
);
insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);
select connect_by_iscycle,sys_connect_by_path(child,'/') path
  from hier2
 start with parent is null
 connect by nocycle prior child = parent;
CONNECT_BY_ISCYCLE    PATH
----------------------------------   -------
                 0                              /1
                 0                              /1/2
                 1                              /1/2/3

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.