DBA Hub

📋Steps in this guide1/3

Hierarchical Queries in Oracle

Easily query hierarchical data in Oracle databases using SQL.

oracle miscconfigurationintermediate
by OracleDba
20 views
1

Setup

The following table contains hierarchical data.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  parent_id NUMBER,
  CONSTRAINT tab1_pk PRIMARY KEY (id),
  CONSTRAINT tab1_tab1_fk FOREIGN KEY (parent_id) REFERENCES tab1(id)
);

CREATE INDEX tab1_parent_id_idx ON tab1(parent_id);

INSERT INTO tab1 VALUES (1, NULL);
INSERT INTO tab1 VALUES (2, 1);
INSERT INTO tab1 VALUES (3, 2);
INSERT INTO tab1 VALUES (4, 2);
INSERT INTO tab1 VALUES (5, 4);
INSERT INTO tab1 VALUES (6, 4);
INSERT INTO tab1 VALUES (7, 1);
INSERT INTO tab1 VALUES (8, 7);
INSERT INTO tab1 VALUES (9, 1);
INSERT INTO tab1 VALUES (10, 9);
INSERT INTO tab1 VALUES (11, 10);
INSERT INTO tab1 VALUES (12, 9);
COMMIT;
2

Basic Hierarchical Query

In its simplest form a hierarchical query needs a definition of how each child relates to its parent. This is defined using the clause, which defines how the current row (child) relates to a prior row (parent). In addition, the START WITH clause can be used to define the root node(s) of the hierarchy. Hierarchical queries come with operators, pseudocolumns and functions to help make sense of the hierarchy. - : The position in the hierarchy of the current row in relation to the root node. - : Returns the root node(s) associated with the current row. - : Returns a delimited breadcrumb from root to the current row. - : Indicates if the current row is a leaf node. - : Applies an order to siblings, without altering the basic hierarchical structure of the data returned by the query. The following query gives an example of these items based on the previously defined test table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SET PAGESIZE 20 LINESIZE 110
COLUMN tree FORMAT A20
COLUMN path FORMAT A20

SELECT id,
       parent_id,
       RPAD('.', (level-1)*2, '.') || id AS tree,
       level,
       CONNECT_BY_ROOT id AS root_id,
       LTRIM(SYS_CONNECT_BY_PATH(id, '-'), '-') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR id
ORDER SIBLINGS BY id;

        ID  PARENT_ID TREE                      LEVEL    ROOT_ID PATH                       LEAF
---------- ---------- -------------------- ---------- ---------- -------------------- ----------
         1            1                             1          1 1                             0
         2          1 ..2                           2          1 1-2                           0
         3          2 ....3                         3          1 1-2-3                         1
         4          2 ....4                         3          1 1-2-4                         0
         5          4 ......5                       4          1 1-2-4-5                       1
         6          4 ......6                       4          1 1-2-4-6                       1
         7          1 ..7                           2          1 1-7                           0
         8          7 ....8                         3          1 1-7-8                         1
         9          1 ..9                           2          1 1-9                           0
        10          9 ....10                        3          1 1-9-10                        0
        11         10 ......11                      4          1 1-9-10-11                     1
        12          9 ....12                        3          1 1-9-12                        1
3

Cyclic Hierarchical Query

It is possible for a hierarchy to be cyclical, which can represent a problem when querying the data. To simplify matters, the clause tells the database not to traverse cyclical hierarchies. In this case the function indicates which record is responsible for the cycle. We can now use the option and check the results of the function. For more information see: - Hierarchical Queries - Hierarchical Query Operators - Hierarchical Query Pseudocolumns - SYS_CONNECT_BY_PATH - Hierarchical Queries in Oracle (CONNECT BY) - Hierarchical Queries in Oracle (Recursive WITH Clause) - Recursive Subquery Factoring : Hierarchical Queries Using Recursive WITH Clauses Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Create a cyclic reference
UPDATE tab1 SET parent_id = 9 WHERE id = 1;
COMMIT;


SELECT id,
       parent_id,
       RPAD('.', (level-1)*2, '.') || id AS tree,
       level,
       CONNECT_BY_ROOT id AS root_id,
       LTRIM(SYS_CONNECT_BY_PATH(id, '-'), '-') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR id
ORDER SIBLINGS BY id;
ERROR:
ORA-01436: CONNECT BY loop in user data

SELECT id,
       parent_id,
       RPAD('.', (level-1)*2, '.') || id AS tree,
       level,
       CONNECT_BY_ROOT id AS root_id,
       LTRIM(SYS_CONNECT_BY_PATH(id, '-'), '-') AS path,
       CONNECT_BY_ISLEAF AS leaf,
       CONNECT_BY_ISCYCLE AS cycle
FROM   tab1
START WITH id = 1
CONNECT BY NOCYCLE parent_id = PRIOR id
ORDER SIBLINGS BY id;

        ID  PARENT_ID TREE                      LEVEL    ROOT_ID PATH                       LEAF      CYCLE
---------- ---------- -------------------- ---------- ---------- -------------------- ---------- ----------
         1          9 1                             1          1 1                             0          0
         2          1 ..2                           2          1 1-2                           0          0
         3          2 ....3                         3          1 1-2-3                         1          0
         4          2 ....4                         3          1 1-2-4                         0          0
         5          4 ......5                       4          1 1-2-4-5                       1          0
         6          4 ......6                       4          1 1-2-4-6                       1          0
         7          1 ..7                           2          1 1-7                           0          0
         8          7 ....8                         3          1 1-7-8                         1          0
         9          1 ..9                           2          1 1-9                           0          1
        10          9 ....10                        3          1 1-9-10                        0          0
        11         10 ......11                      4          1 1-9-10-11                     1          0
        12          9 ....12                        3          1 1-9-12                        1          0

Comments (0)

Please to add comments

No comments yet. Be the first to comment!