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. Read about the new features in Oracle 10g that help with Hierarchical Queries.

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. View Listing A.

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

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. For instance, view Listing C.

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). Check out Listing D.

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, as in Listing E.

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;

—————————   ———-
         0             /1
         0             /1/2
         1             /1/2/3

TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox