Oracle - Recursive query is not recursing (not as much as I'd like)

Question

I had an idea that I could write a query to find all the decendent tables of a root table, based on foreign keys.

Query looks like this:

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 I get look like this:

        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 the child tables of ROOT_TAB, but the query does not recurse to the children of CHILD_TAB_1, CHILD_TAB_2, or CHILD_TAB_3.

Recursive queries are new to me so I'm guessing I'm missing something in the connect by clause, but I'm drawing a blank here. Is it actually possible to get the full hierarchy of ROOT_TAB in a single query, or am I better off wrapping the query in a recursive procedure?

(Oracle 10g)

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. That is fine for connecting the first child to the root table, but it is not what you need to connect the children on the second level to the first. That is why you need to join against the constraints twice -- once to get the table's primary key, once to get the foreign keys.

As an aside, if you are going to be querying the all_* views rather than the user_* views, you generally want to join them on table_name AND owner, not just table_name. If multiple schemas have tables with the same name, joining on just table_name will give incorrect results.