Introduction

Sometimes it is required to store tree-like structures in a relational database and query those structures in a way which preserves the hierarchy of the tree. Lets see how this can be done in the Oracle RDBMS using hierarchical queries.

First, we create a sample schema and insert some data:

-- Create the table which holds the tree nodes
-- Parent is the foreign key which refers to the Id of the parent node
CREATE TABLE TreeNode (
    Id NUMBER(10) NOT NULL,
    Parent NUMBER(10),
    Label VARCHAR2(100)
);
CREATE UNIQUE INDEX TreePK ON TreeNode(Id);
CREATE INDEX TreeParentPK ON TreeNode(Parent);

INSERT INTO TreeNode VALUES(1, 0, 'Root Node');
INSERT INTO TreeNode VALUES(2, 1, 'Node 1');
INSERT INTO TreeNode VALUES(3, 2, 'Node 1.1');
INSERT INTO TreeNode VALUES(4, 2, 'Node 1.2');
INSERT INTO TreeNode VALUES(5, 4, 'Node 1.2.1');
INSERT INTO TreeNode VALUES(6, 5, 'Node 1.2.1.1');
INSERT INTO TreeNode VALUES(7, 1, 'Node 2');
INSERT INTO TreeNode VALUES(8, 7, 'Node 2.1');
INSERT INTO TreeNode VALUES(9, 7, 'Node 2.3');
INSERT INTO TreeNode VALUES(10, 7, 'Node 2.4');
INSERT INTO TreeNode VALUES(11, 1, 'Node 3');
INSERT INTO TreeNode VALUES(12, 1, 'Node 4');
INSERT INTO TreeNode VALUES(13, 12, 'Node 4.1');

COMMIT;

(Note that we missed out “Node 2.2” – we will come back to this later.)

The above sample represents the following tree structure:

In the next section, we see how to query the tree which we just created.