Nested Sets in Oracle (5)


Now that we have started to compare the nested set model with the Oracle hierarchical model in Nested Sets in Oracle (4), I continue with the nested sets part of Managing Hierarchical Data in MySQL.

Depth of a Sub-Tree

This one is easy. We just modify the standard tree walking query to start at the top of the sub-tree.

SELECT
    name,
    LEVEL - 1
      AS depth
  FROM
    category
  START WITH
    name = 'PORTABLE ELECTRONICS' -- Identify the root of the sub-tree
  CONNECT BY
    parent = PRIOR category_id
/

The output is:

NAME                      DEPTH
-------------------- ----------
PORTABLE ELECTRONICS          0
MP3 PLAYERS                   1
FLASH                         2
CD PLAYERS                    1
2 WAY RADIOS                  1

This time the Oracle query is the better solution.

Find the Immediate Subordinates of a Node

We modify the preceding query to limit the depth of the tree walked.

SELECT
    name,
    LEVEL - 1
      AS depth
  FROM
    category
  WHERE
    LEVEL <= 2 -- Limit the depth of the sub-tree walked
  START WITH
    name = 'PORTABLE ELECTRONICS' -- Identify the root of the sub-tree
  CONNECT BY
    parent = PRIOR category_id
/

The output is:

NAME                      DEPTH
-------------------- ----------
PORTABLE ELECTRONICS          0
MP3 PLAYERS                   1
CD PLAYERS                    1
2 WAY RADIOS                  1

Another win for Oracle!

Aggregate Functions in a Nested Set

Because the kludge needed to set up auto-increment columns in Oracle (as described in Nested Sets in Oracle (1)), I changed the definition of the product table to be:

CREATE TABLE product (
      product_id
        NUMBER
        CONSTRAINT product_pk
          PRIMARY KEY,
      name
        VARCHAR2(20)
        CONSTRAINT product_name_nn
          NOT NULL
        CONSTRAINT product_name_uk
          UNIQUE,
      category_id
        NUMBER
        CONSTRAINT product_category_id_nn
          NOT NULL
        CONSTRAINT product_category_id_fk
          REFERENCES category( category_id )
)
/

I have inferred the unique key constraint for the name of the product, and the foreign key relationship for the category_id column.

To load the data, I use a similar script to that described in Nested Sets in Oracle (1).

-- Remove all previous data
TRUNCATE TABLE product;
INSERT INTO product( product_id, name, category_id )
  VALUES(  1, '20" TV',            get_category_id( 'TUBE' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  2, '36" TV',            get_category_id( 'TUBE' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  3, 'Super-LCD 42"',     get_category_id( 'LCD' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  4, 'Ultra-Plasma 62"',  get_category_id( 'PLASMA' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  5, 'Value Plasma 38"',  get_category_id( 'PLASMA' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  6, 'Power-MP3 128mb',   get_category_id( 'MP3 PLAYERS' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  7, 'Super-Shuffle 1gb', get_category_id( 'FLASH' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  8, 'Porta CD',          get_category_id( 'CD PLAYERS' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES(  9, 'CD To go!',         get_category_id( 'CD PLAYERS' ) );
INSERT INTO product( product_id, name, category_id )
  VALUES( 10, 'Family Talk 360',   get_category_id( '2 WAY RADIOS' ) );
COMMIT;

I cannot think of a way to do:

Now let’s produce a query that can retrieve our category tree, along with a product count for each category:

Advertisements

3 thoughts on “Nested Sets in Oracle (5)

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

  2. Hello Douglas,

    thanks for such a good article,

    …I have the requirement to handle hierarchical data in oracle 8i, I would like to use the nested sets method described at the MySQL article, how would you translate this MySQL query to Oracle(8i):

    Find the Immediate Subordinates of a Node

    SELECT node.name, (COUNT(parent.name) – (sub_tree.depth + 1)) AS depth
    FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
    SELECT node.name, (COUNT(parent.name) – 1) AS depth
    FROM nested_category AS node,
    nested_category AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.name = ‘PORTABLE ELECTRONICS’
    GROUP BY node.name
    ORDER BY node.lft
    )AS sub_tree
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
    GROUP BY node.name
    HAVING depth <= 1
    ORDER BY node.lft;

    I would appreciate your help.
    Regards
    Damian

    • Damian,

      I am sorry but I have not used Oracle 8i for over six (6) years. I do not have access to any Oracle 8i systems to test code out on.

      From my reading of the original article on the MySQL code for Nested Sets, the implementation was compatible with SQL 92. I think if you follow the implementation from MySQL you might be safe.

      The intention of my series of posts was to see how I could implement the same functionality using Oracle XE.

      Douglas

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