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.

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.

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                               /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

Do you need help with Oracle? Gain advice from Builder AU forums

Related links

Comments

1

Aman Greval - 15/11/06

Very well written, and very clear explanation

» Report offensive content

2

Aman Greval - 15/11/06

Very well written, and very clear explanation

» Report offensive content

3

VARINDER KAMBOJ - 19/12/06

GREAT ARTICLE,GOOD EXPLANATION

» Report offensive content

4

Ram - 29/03/07

The explanation is very clear with such an easy to understand example. Very appreciable!

» Report offensive content

5

Rakesh Sayini - 26/05/07

Wonderful Explanation with Examples.Connect By prior ,start with and some more new points are explained point to point.

Every thing is covered in above examples

» Report offensive content

6

PRATAP SAHOO - 03/10/07

CONNECT_BY_ISLEAF and connect_by_root child both have excellent performances for hierachy query.

its great! and really advanced feature of oracle10g

» Report offensive content

7

Shini - 18/10/07

Great article, your write-up is informative and damn good!
Will keep visiting this site for more.... :)

» Report offensive content

8

Sajith OD - 25/09/08

Very good article 

select connect_by_isleaf,sys_connect_by_path(child,'/') path

  from hier

where connect_by_leaf=1

 start with parent is null

 connect by prior child = parent;



whether above query can use to extract  path of leaf nodes ?



if structure of data is like given below:



depid    empname

1           sajithod

1           jibindas

1           vijay

2           murali

2           ganesh

» Report offensive content

Leave a comment

You must read and type the 6 chars within 0..9 and A..F

* indicates mandatory fields.

Latest comments

8

Sajith OD - 25/09/08

Very good article select connect_by_isleaf,sys_connect_by_path(child,'/') path from hier where connect_by_leaf=1 start with parent is null connect by prior child = parent; whether ... more

7

Shini - 18/10/07

Great article, your write-up is informative and damn good! Will keep visiting this site for more.... :) ... more

6

PRATAP SAHOO - 10/03/07

CONNECT_BY_ISLEAF and connect_by_root child both have excellent performances for hierachy query. its great! and really advanced feature of oracle10g ... more

Log in


Sign up | Forgot your password?

Blogs

RSS feed

What's on?