A recursive query for table dependencies does not recurs as much as we would like

Question

I got the idea that I can write a query to find all subsequent tables of the root table based on foreign keys.

The request is as follows:

select level, lpad(' ', 2 * (level - 1)) || uc.table_name as "TABLE", uc.constraint_name, uc.r_constraint_name from all_constraints uc where uc.constraint_type in ('R', 'P') start with uc.table_name = 'ROOT_TAB' connect by nocycle prior uc.constraint_name = uc.r_constraint_name order by level asc; 

The results that I get are as follows:

  1 ROOT_TAB XPKROOTTAB  
         1 ROOT_TAB R_20 XPKPART_TAB
         2 CHILD_TAB_1 R_40 XPKROOTTAB
         2 CHILD_TAB_2 R_115 XPKROOTTAB
         2 CHILD_TAB_3 R_50 XPKROOTTAB

This result is all ROOT_TAB child tables, but the query does not regenerate child elements CHILD_TAB_1 , CHILD_TAB_2 or CHILD_TAB_3 .

Recursive queries are new to me, so I assume that I missed something in the connect by clause, but here I draw a space. Is it possible to get the full ROOT_TAB hierarchy in a single query, or is it better for me to wrap the query in a recursive procedure?

Answer 1

You want something like this:

 select t.table_name, level,lpad(' ', 2 * (level - 1))||t.table_name from user_tables t join user_constraints c1 on (t.table_name = c1.table_name and c1.constraint_type in ('U', 'P')) left join user_constraints c2 on (t.table_name = c2.table_name and c2.constraint_type='R') start with t.table_name = 'ROOT_TAB' connect by prior c1.constraint_name = c2.r_constraint_name 

The problem with the original query is that uc.constraint_name for the child table is the name of the foreign key. This is normal for connecting the first child to the root table, but you do not need to connect the children in the second level to the first. This is why you need to combine with constraints twice - once to get the primary key of the table, once to get foreign keys.

As an aside, if you are going to request the views all_ *, not user_ *, you usually want to append them to table_name and owner, and not just to the table name. If several schemas have tables with the same name, joining only with the name table_name will give incorrect results.

Answer 2

For the case with multiple schemas and multiple root tables, try something like:

 WITH constraining_tables AS (SELECT owner, constraint_name, table_name FROM all_constraints WHERE owner LIKE 'ZZZ%' AND constraint_type IN ('U', 'P')), constrained_tables AS (SELECT owner, constraint_name, table_name, r_owner, r_constraint_name FROM all_constraints WHERE owner LIKE 'ZZZ%' AND constraint_type = 'R'), root_tables AS (SELECT owner, table_name FROM constraining_tables MINUS SELECT owner, table_name FROM constrained_tables) SELECT c1.owner || '.' || c1.table_name, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || c1.owner || '.' || c1.table_name FROM constraining_tables c1 LEFT JOIN constrained_tables c2 ON c1.owner = c2.owner AND c1.table_name = c2.table_name START WITH c1.owner || '.' || c1.table_name IN (SELECT owner || '.' || table_name FROM root_tables) CONNECT BY PRIOR c1.constraint_name = c2.r_constraint_name 

Answer 3

After deep in-depth research, I made my own version, which processes all tables and returns the level of the maximum table in the hierarchy (it reads all the schemes, taking into account tables without parent-child relationships, which will be at level 1 along with the root ones). If you have access, use the dba_ tables instead of all_ units.

  WITH hier AS ( SELECT child_table owner_table_name , LEVEL lvl , LPAD (' ', 4 * (LEVEL - 1)) || child_table indented_child_table , sys_connect_by_path( child_table, '|' ) tree FROM ( /*----------------------------------------------------------------------*/ /* Retrieve all tables. Set them as the Child column, and set their */ /* Parent Column to NULL. This is the root list (first iteration) */ /*----------------------------------------------------------------------*/ SELECT NULL parent_table , a.owner || '.' || a.table_name child_table FROM all_tables a UNION /*----------------------------------------------------------------------*/ /* List of all possible Parent-Child relations. This table is used as */ /* a link list, to link the current iteration with the next one, from */ /* root to last child (last child is what we are interested to find). */ /*----------------------------------------------------------------------*/ SELECT p.owner || '.' || p.table_name parent_table , c.owner || '.' || c.table_name child_table FROM all_constraints p, all_constraints c WHERE p.owner || '.' || p.constraint_name = c.r_owner || '.' || c.r_constraint_name AND (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' ) START WITH parent_table IS NULL /*----------------------------------------------------------------------*/ /* NOCYCLE prevents infinite loops (ie self referencing table constr) */ /*----------------------------------------------------------------------*/ CONNECT BY NOCYCLE PRIOR child_table = parent_table ) SELECT * FROM hier WHERE (owner_table_name, lvl) IN ( SELECT owner_table_name , MAX(lvl) FROM hier GROUP BY owner_table_name ); 

Edit: There is a “kinda” problem with this query when searching for infinite loops.

If we have this tree:

 b --> c --> d b <-- c 

it will assign lvl 2 to c as follows: b --> c and lvl 2 - b as: c --> b

for d, it will detect b --> c --> d so it will assign lvl 3

So, as you can see, the problem is inside the loop, the values ​​from the outside will always have the maximum correct lvl