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;

CONNECT_BY_ISCYCLE    PATH
——————   ——-
         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!