Oracle table hierarchy level and grouping

Question

I have a oracle database where foreign key is enabled and I have tables A,B,C,D,E,F,AB,ABC,EF Attaching the picture for hierarchy 'B' has parent 'D', 'AB' has parent 'A' as well as 'B', 'ABC' has parent 'AB' and 'C', 'EF' has parent 'E' & 'F',

Now {A,B,C,D,AB, ABC} has no link with {E,F,EF}. I have 2 requirements

1. I need to group them as group 1 & 2
2. I need to assign a hierarchy level for each group maintaining referential integrity as follows

How can I do it using sql/pl/sql in oracle database?

Answer 1

Assuming that you always end up at a single leaf node for each group then you can use a hierarchical query twice: from leaf-to-root to determine the groups; and then from root-to-leaf to determine the depths.

WITH groups ( child, parent, grp ) AS (
  SELECT child,
         parent,
         DENSE_RANK() OVER ( ORDER BY CONNECT_BY_ROOT( child ) )
  FROM   table_name t
  START WITH child NOT IN ( SELECT parent FROM table_name )
  CONNECT BY PRIOR parent = child
),
depths ( child, parent, grp, depth ) AS (
  SELECT child, parent, grp, LEVEL
  FROM   groups
  START WITH parent NOT IN ( select child FROM table_name )
  CONNECT BY PRIOR child = parent
)
SELECT table_name,
       grp,
       MAX( depth + depth_modifier ) AS depth
FROM   depths
UNPIVOT ( table_name FOR depth_modifier IN ( child AS 1, parent AS 0 ) )
GROUP BY grp, table_name
ORDER BY grp, depth, table_name

Which, for your sample data:

CREATE TABLE table_name ( child, parent ) AS
SELECT 'B',   'D'  FROM DUAL UNION ALL
SELECT 'AB',  'A'  FROM DUAL UNION ALL
SELECT 'AB',  'B'  FROM DUAL UNION ALL
SELECT 'ABC', 'AB' FROM DUAL UNION ALL
SELECT 'ABC', 'C'  FROM DUAL UNION ALL
SELECT 'EF',  'E'  FROM DUAL UNION ALL
SELECT 'EF',  'F'  FROM DUAL;

Outputs:

TABLE_NAME | GRP | DEPTH
:--------- | --: | ----:
A          |   1 |     1
C          |   1 |     1
D          |   1 |     1
B          |   1 |     2
AB         |   1 |     3
ABC        |   1 |     4
E          |   2 |     1
F          |   2 |     1
EF         |   2 |     2

If you don't always have a single leaf node for each group then you'll need a MUCH more complicated solution (probably written in PL/SQL).

db<>fiddle here