How to build a hierarchy in Oracle SQL as efficiently as possible

Question

This is in Oracle SQL using their BI Publisher Data Model tool

Say you have two tables:

hierarchy_table acct_description_table

and in these two tables you have these columns:

hierarchy_table
+------------+-----------+-------+
| primarykey | parentkey | depth |
+------------+-----------+-------+

acct_description_table
+------------+-------------+
| acct_value | description |
+------------+-------------+

and with this data you want to build out a hierarchy like so (descriptions omitted):

+----------------+----------------+----------------+
|  acct_depth_0  |  acct_depth_1  |  acct_depth_2  | . . .
+----------------+----------------+----------------+
|     450000     |     450040     |                | . . .
+----------------+----------------+----------------+
|     450000     |     450050     |     450051     | . . .
+----------------+----------------+----------------+

What would be the best way to achieve this? So far I have the following SQL:

select distinct
  t1.acct,
  t1.desc,
  t2.acct,
  t2.desc,
  t3.acct,
  t3.desc,
  t4.acct,
  t4.desc,
  t5.acct,
  t5.desc,
  t6.acct,
  t6.desc
from (select tree.primarykey acct, act.description desc 
      from hierarchy_table tree, acct_description_table act 
      where 1=1
        and tree.depth = 0
        and act.acct_value = tree.primarykey
     ) t1
left join (select tree.primarykey acct, tree.parentkey parent, act.description desc 
           from hierarchy_table tree, acct_description_table act 
           where 1=1
             and tree.depth = 1
             and act.acct_value = tree.primarykey
     ) t2
  on 1=1 and t1.acct = t2.parent
...
...
...
left join (select tree.primarykey acct, tree.parentkey parent, act.description desc 
           from hierarchy_table tree, acct_description_table act 
           where 1=1
             and tree.depth = 5
             and act.acct_value = tree.primarykey
     ) t6
  on 1=1 and t5.acct = t6.parent

As you can see with a query like this we'd be doing 5 different left join operations to complete the table. I've looked into recursion to help speed up this query. However, I'm not sure how to code it to work with our need of having columns corresponding to each depth.

Has anyone done something similar to this? Or does anyone know of a way that we could do this faster than the current query consisting of 5 different left joins? Thank you!

Matthew McPeak · Accepted Answer · 2020-08-31 19:25:09Z

Oracle's CONNECT BY functionality is good for hierarchies and pretty efficient. Here's one way you might use it for your data:

with hierarchy_table ( primarykey, parentkey, depth ) as 
-- This is test data, you would not have this WITH clause...
( SELECT '450000', null, 0 FROM DUAL UNION ALL
  SELECT '450040', '450000', 1 FROM DUAL UNION ALL
  SELECT '450050', '450000', 1 FROM DUAL UNION ALL  
  SELECT '450051', '450050', 2 FROM DUAL ),
    acct_description_table ( acct_value, description ) AS
-- This is test data, you would not have this WITH clause...
( SELECT '450000', 'Acct #450000' FROM DUAL UNION ALL
  SELECT '450040', 'Acct #450040' FROM DUAL UNION ALL
  SELECT '450050', 'Acct #450050' FROM DUAL UNION ALL  
  SELECT '450051', 'Acct #450051' FROM DUAL )
-- Real query starts here
SELECT REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,1) acct1,
       REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,2) acct2,
       REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,3) acct3,
       REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,4) acct4,
       REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,5) acct5,
       REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,6) acct6,
       adt.description
FROM    hierarchy_table ht
INNER JOIN acct_description_table adt ON adt.acct_value = ht.primarykey
WHERE CONNECT_BY_ISLEAF = 1
START WITH ht.parentkey IS NULL
CONNECT BY ht.parentkey = PRIOR ht.primarykey
AND ht.depth = PRIOR ht.depth + 1  -- There is actually no need for the "DEPTH" column in your table.
AND LEVEL <= 6;

+--------+--------+--------+-------+-------+-------+--------------+
| ACCT1  | ACCT2  | ACCT3  | ACCT4 | ACCT5 | ACCT6 | DESCRIPTION  |
+--------+--------+--------+-------+-------+-------+--------------+
| 450000 | 450040 |        |       |       |       | Acct #450040 |
| 450000 | 450050 | 450051 |       |       |       | Acct #450051 |
+--------+--------+--------+-------+-------+-------+--------------+

Wow this is a great answer! I implemented this and it pulls the hierarchy very quickly. Your query is 20 lines (with a few changes on my part) vs our previous query being 80+ lines. Very nice!

Collectives™ on Stack Overflow

How to build a hierarchy in Oracle SQL as efficiently as possible

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related