Nested Sets in Oracle (6)


I continue to compare the nested set model with the Oracle hierarchical model from where I left off in Nested Sets in Oracle (5).

I now have a way to do:

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

Aggregate Functions in a Nested Set (cont.)

I have created a function to return the number of rows that are joined between the nodes of a sub-tree of categories with the PRODUCT table. This is a variation of the sub-tree query shown in Depth of a Sub-Tree.

CREATE OR REPLACE FUNCTION num_of_products_for_category(
    p_category_name IN VARCHAR2
  )
  RETURN NUMBER
  AS
    l_product_count
      NUMBER
      NOT NULL
      := 0;
  BEGIN
    SELECT
        COUNT(*)
      INTO
        l_product_count
      FROM (
        SELECT
            category_id
          FROM
            category
          START WITH
            name = p_category_name
          CONNECT BY
            parent = PRIOR category_id
        ) cat
      INNER JOIN
        product
      USING (
        category_id
      );
    RETURN l_product_count;
END num_of_products_for_category;
/

The query to reproduce the MySQL example is:

SELECT
    name,
    num_of_products_for_category( name )
      AS category_count
  FROM
    category
/

And the output is:

NAME                 NUM_OF_PRODUCTS_FOR_CATEGORY(NAME)
-------------------- ----------------------------------
2 WAY RADIOS                                          1
CD PLAYERS                                            2
ELECTRONICS                                          10
FLASH                                                 1
LCD                                                   1
MP3 PLAYERS                                           2
PLASMA                                                2
PORTABLE ELECTRONICS                                  5
TELEVISIONS                                           5
TUBE                                                  2

10 rows selected.

I do not know how to do a purely SQL version as everytime I unroll the function into the query, I get an error about the correlated column in the START WITH clause.

Adding New Nodes

This is where the Oracle implementation powers ahead of the Nested Sets model.

Insert the new node with the following DML:

INSERT INTO category ( name, parent )
  VALUES( 'GAME CONSOLES', get_category_id( 'ELECTRONICS' ) );
COMMIT;

There is no shuffling of pointers to add a new node. From the original article, I get the impression that the order of the siblings is important. Don’t know why?

If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let’s add a new FRS node below the 2 WAY RADIOS node:

INSERT INTO category ( name, parent )
  VALUES( 'FRS', get_category_id( '2 WAY RADIOS' ) );
COMMIT;

Running the query Retrieving a Full Tree

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

12 rows selected.

Deleting Nodes

To delete a leaf, use:

DELETE FROM category WHERE name = 'GAME CONSOLES';
COMMIT;

This is straight forward because of the uniqueness constraint on the NAME column.

To delete a sub-tree, use:

DELETE FROM product
  WHERE category_id IN (
    SELECT
        category_id
      FROM
        category
      START WITH
        name = 'MP3 PLAYERS'
      CONNECT BY
        parent = PRIOR category_id
    )
;
DELETE FROM category
  WHERE category_id IN (
    SELECT
        category_id
      FROM
        category
      START WITH
        name = 'MP3 PLAYERS'
      CONNECT BY
        parent = PRIOR category_id
    )
;
COMMIT;

This is complicated because of the RI between PRODUCT and CATEGORY (something you did have to worry about in MySQL 4).

The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent:

UPDATE category
  SET parent = (SELECT parent FROM category WHERE name = 'PORTABLE ELECTRONICS' )
  WHERE parent = (SELECT category_id FROM category WHERE name = 'PORTABLE ELECTRONICS' )
;
DELETE FROM category WHERE name = 'PORTABLE ELECTRONICS';
COMMIT;

If the function GET_CATEGORY_ID is used, then the following message will appear:

ERROR at line 3:
ORA-04091: table MBRUCE.CATEGORY is mutating, trigger/function may not see it
ORA-06512: at "MBRUCE.GET_CATEGORY_ID", line 39

And the resulting tree is:

NAME
----------------
ELECTRONICS
. CD PLAYERS
. 2 WAY RADIOS
. . FRS
. TELEVISIONS
. . LCD
. . TUBE
. . PLASMA

8 rows selected.

Final Thoughts

I hope I have convinced you that nested sets are a bad idea. The manipulation of pointers is just too error-prone.

Oracle’s hierarchical queries are mostly satisfactory. There were a few cases where the Oracle query was more complicated and one (1) case where I had to resort to using a PL/SQL function.

Advertisements

One thought on “Nested Sets in Oracle (6)

  1. What you are describing is a cross between a materialized path and adjacency list and NOT a nested set.

    A nested set never references a parent node but rather uses a lft to rgt identify levels (nearest nodes) in the tree.

    I suggest you read up on Joe Celko “Trees and Hierarchies”

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