Nested Sets in Oracle (3)


Now that the data has been loaded into the CATEGORY table as described in Nested Sets in Oracle (2), I now move onto the data reporting part of Managing Hierarchical Data in MySQL.

Retrieving a Full Tree

In Oracle, there are Hierarchical Queries . A traditional way to produce a tree is to use a query like:

SELECT
    LPAD( name, (LEVEL-1)*2 + LENGTH( name ), '. ' )
	 AS name
  FROM
    category
  START WITH
    parent IS NULL
  CONNECT BY
    parent = PRIOR category_id
/

The output is:

NAME
----------------------
ELECTRONICS
. PORTABLE ELECTRONICS
. . MP3 PLAYERS
. . . FLASH
. . CD PLAYERS
. . 2 WAY RADIOS
. TELEVISIONS
. . LCD
. . TUBE
. . PLASMA

10 rows selected.

In this case, we did not had to explicitly identify the root node. We specified a programmatic definition: a root node has no parent. Starting with this definition, we then told the server to walk down the tree by using the CATEGORY_ID of the parent node to find its children as those who have the PARENT value equal to the parent’s CATEGORY_ID which is calculated by the PRIOR operator.

The pretty-printing is done using the LEVEL Pseudocolumn (which starts at 1 and increments the further you descend the tree). The LPAD does all the work of inserting the leading characters to emphasis the hierarchy.

Finding all the Leaf Nodes

The easiest way to find all the leaves in the tree (nodes that have no children) is to use the CONNECT_BY_ISLEAF Pseudocolumn:

SELECT
    name
  FROM
    category
  WHERE
    CONNECT_BY_ISLEAF = 1 -- is a leaf
  START WITH
    parent IS NULL
  CONNECT BY
    parent = PRIOR category_id
/

The output is:

NAME
--------------------
FLASH
CD PLAYERS
2 WAY RADIOS
LCD
TUBE
PLASMA

6 rows selected.

Retrieving a Single Path

I am going to simplify the example by printing the tree done to the specified node through the use of the SYS_CONNECT_BY_PATH function:

SELECT
    SYS_CONNECT_BY_PATH( name, ' -> ' )
	  AS path
  FROM
    category
  WHERE
    name = 'FLASH'
  START WITH
    parent IS NULL
  CONNECT BY
    parent = PRIOR category_id
/

The output is:

PATH
---------------------------------------------------------------
 -> ELECTRONICS -> PORTABLE ELECTRONICS -> MP3 PLAYERS -> FLASH

Apart from the extra ‘ -> ‘ in front of the path, this solution is much simpler than the MySQL example and is not limited by the number of levels.

Advertisements

3 thoughts on “Nested Sets in Oracle (3)

  1. Nested Sets in Oracle (4) « Yet Another OCM

  2. Nested Sets in Oracle (6) « Yet Another OCM

  3. NORDIC Schema for TopLink Demonstration (3)4 « Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s