In a comment on my post about getting all the foreign key relationships, I was asked how to get the shortest path between two tables. Not too easy…
UPDATE: you can see how hard it is by the fact I have had to correct two bugs, one concerning direct or indirect self-references.
Using the ALL_CONSTRAINTS view, we can put together all the foreign key relationships between child tables and parent tables, and we can do hierarchical queries to go up and down branches of the hierarchy. If you give me two tables, though, how do I know whether to go up or down, and which table to start with?
I decided to start with both tables and go up. I expect children to outnumber parents, so going up should be more efficient. I start with both tables, knowing that at most one will be the descendant of the other. I then go up the hierarchy until I either get to the top or hit one of the tables. The “shortest path” is the one where the final LEVEL is smallest.
I start with a little subquery that provides the target tables.
with search_for (owner, table_name) as ( select 'OBE', 'OEHR_COUNTRIES' from dual union all select 'OBE', 'OEHR_ORDER_ITEMS' from dual )
Then I get all the constraints that may be involved in foreign key relationships.
, pur as ( select OWNER, CONSTRAINT_NAME, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME from dba_constraints where constraint_type in('P','U','R') and owner in (select username from dba_users where oracle_maintained = 'N') ) and owner in (select username from dba_users where oracle_maintained = 'N') )
I do a self join on the PUR subquery to add the parent table to each row.
UPDATE: added a line to RELATIONS to avoid self-references.
, relations as ( select a.owner, a.table_name, a.r_owner, b.table_name r_table_name from pur a join pur b on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME)) and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name)) )
Now I start going up the hierarchy:
- I “start with” both tables
- I “connect by” going up the hierarchy, stopping before the other table can be a child
This is important, because the other table could be at the top of the hierarchy and not be a child at all.
- The WHERE clause is applied after the CONNECT BY: I only keep rows where I found the other table.
- I now have one row for each path from one table to the other. I use SYS_CONNECT_BY_PATH to concatenate the original child and all the parents going up the hierarchy.
Notice I have to use one literal (<) within the first parameter and another (/) as the second parameter. I wanted to use ‘<‘ both places, but Oracle won’t let me.
- Finally, I choose the shortest path, translate ‘/’ to ‘<‘, get rid of the first ‘<‘ and display the result.
- To avoid indirect self-referencing, I make sure the last remote table is not the same and the original table!
- I had the ‘<‘ outside of the CASE statement. Now it is inside so you do not get ‘<<‘ anymore.
, paths as ( select row_number() over( order by case when (r_owner, r_table_name) != ((connect_by_root(owner), connect_by_root(table_name))) then level end ) rn, sys_connect_by_path( case level when 1 then owner||'.'||table_name || '<' end || r_owner||'.'||r_table_name, '/') path from relations r where (r_owner, r_table_name) in (select * from search_for) start with (owner, table_name) in (select * from search_for) connect by nocycle (owner, table_name) = ((prior r_owner, prior r_table_name)) and (owner, table_name) not in (select * from search_for) ) select substr(translate(path,'/','<'), 2) best_path from paths where rn = 1; BEST_PATH ------------------------------------------------------------------------------------------------------------------ OBE.OEHR_ORDER_ITEMS<OBE.OEHR_ORDERS<OBE.OEHR_EMPLOYEES<OBE.OEHR_DEPARTMENTS<OBE.OEHR_LOCATIONS<OBE.OEHR_COUNTRIES
As the man said, hope this helps…