Nested Sets in Oracle (4)


Now that we have explored how to report on the adjacency list model using Oracle as described in Nested Sets in Oracle (3), I now move onto the nested sets part of Managing Hierarchical Data in MySQL.

Limitations of the Adjacency List Model

Since the author does give an examples of the inadequacies of the model, I will not address them here but show how the current model is adequate given the power of Oracle 10G SQL.

The Nested Set Model

I am not going to change the CATEGORY table as I had created it in Nested Sets in Oracle (1) because it is adequate for the purposes of demonstration.

As the author tries to do, the construction of the left and right pointers for a binary tree is error-prone. I think my data loading script in Nested Sets in Oracle (2) is more intuitive but it is still inelegant. The idea is to let the computer work out the internal links rather than the user.

Retrieving a Full Tree

The query given in Nested Sets in Oracle (3) is unchanged. There is no need for a join, sorting, or for the specification of the root node by name in my version of the query.

Finding all the Leaf Nodes

The query given in the MySQL article is much simpler than the one I produced in Nested Sets in Oracle (3).

Retrieving a Single Path

There is no easy way to produce the same output in Oracle. One SQL solution is:

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

I use the tree_path subquery to return a single row that contains the path expressed as concatenated list of node names delimited by ‘/’. I then walk the tree again checking the name of each node against the returned path. This is definitely ugly.

The output is:

NAME
--------------------
ELECTRONICS
PORTABLE ELECTRONICS
MP3 PLAYERS
FLASH

Finding the Depth of the Nodes

This is trivial in Oracle:

SELECT
    name,
	LEVEL - 1
      AS depth
  FROM
    category
  START WITH
    parent IS NULL
  CONNECT BY
    parent = PRIOR category_id
/

The output is:

NAME                      DEPTH
-------------------- ----------
ELECTRONICS                   0
PORTABLE ELECTRONICS          1
MP3 PLAYERS                   2
FLASH                         3
CD PLAYERS                    2
2 WAY RADIOS                  2
TELEVISIONS                   1
LCD                           2
TUBE                          2
PLASMA                        2

10 rows selected.
Advertisements

One thought on “Nested Sets in Oracle (4)

  1. Nested Sets in Oracle (5) « 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