Access Path Suggestor

Reverse engineering an existing schema or creating a new sql query. Join access paths are often following foreign keys. Here is a query that searches foreign key dependency paths between two tables.

with fks as(
select pk.owner o1, pk.table_name t1, fk.constraint_name, fk.owner o2, fk.table_name t2
  from all_constraints fk, all_constraints pk
 where fk.r_owner= pk.owner
   and fk.r_constraint_name = pk.constraint_name
   and fk.constraint_type = 'R' 
), pths as (
 select o1,t1,constraint_name,o2,t2,'-<' dir
   from fks
 union all
 select o2,t2,constraint_name,o1,t1,'>-' dir
   from fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth) as (
 select o1,t1,constraint_name,o2,t2,dir,1,o1||'.'||t1||dir||constraint_name||'-'||o2||'.'||t2
   from pths
 where o1 = :owner1
   and t1 = :table1
 union all 
 select s.o1,s.t1,s.constraint_name,s.o2,s.t2
      , s.dir,lvl+1
      , prio.pth||s.dir||case when s.constraint_name not like 'SYS\_%' escape '\' then s.constraint_name||'-' end||s.o2||'.'||s.t2
  from rcte prio inner join pths s on prio.o2=s.o1 and prio.t2=s.t1 and prio.constraint_name != s.constraint_name
 where prio.lvl < 7
) cycle o1,t1 set cycle to 1 default 0
 select lvl,cycle,pth
   from rcte
 where o2 = :owner2
   and t2 = :table2
  order by lvl,pth
;