使用递归查询构建表依赖图 [英] Building a Table Dependency Graph With A Recursive Query

问题描述

我试图根据表之间的外键建立表的依赖关系图.该图需要以任意表名作为根开始.给定一个表名，我可以使用all_constraints视图查找引用该表的表，然后查找引用它们的表，依此类推，但是这样的效率很低.我编写了一个对所有表都执行此操作的递归查询，但是当我添加时:

I am trying to build a dependency graph of tables based on the foreign keys between them. This graph needs to start with an arbitrary table name as its root. I could, given a table name look up the tables that reference it using the all_constraints view, then look up the tables that reference them, and so on, but this would be horrible inefficient. I wrote a recursive query that does this for all tables, but when I add:

START WITH Table_Name=:tablename

它不会返回整个树.

推荐答案

    select parent, child, level from (
select parent_table.table_name parent, child_table.table_name child
 from user_tables      parent_table,
      user_constraints parent_constraint,
      user_constraints child_constraint,
      user_tables      child_table
where parent_table.table_name = parent_constraint.table_name
  and parent_constraint.constraint_type IN( 'P', 'U' )
  and child_constraint.r_constraint_name = parent_constraint.constraint_name
  and child_constraint.constraint_type   = 'R'
  and child_table.table_name = child_constraint.table_name
  and child_table.table_name != parent_table.table_name
)
start with parent = 'DEPT'
connect by prior child = parent

假设所有内容都在同一模式下，

应该可以工作(当然，替换表名).如果需要处理跨模式依赖关系，请使用数据字典表的DBA_版本和OWNER和R_OWNER列的条件.进一步考虑，这也不考虑自我引用约束(即，对EMP表的约束，即MGR列引用EMPNO列)，因此，如果需要处理，则必须修改代码以处理这种情况.具有自我指称的约束.

should work (replace the table name, of course) assuming that everything is in the same schema. Use the DBA_ versions of the data dictionary tables and conditions for the OWNER and R_OWNER columns if you need to handle cross-schema dependencies. On further reflection, this does not account for self-referential constraints (i.e. a constraint on the EMP table that the MGR column references the EMPNO column) either, so you'd have to modify the code to handle that case if you need to deal with self-referential constraints.

出于测试目的，我在SCOTT模式中添加了一些新表，这些表还引用了DEPT表(包括孙代依赖项)

For testing purposes, I added a few new tables to the SCOTT schema that also reference the DEPT table (including a grandchild dependency)

SQL> create table dept_child2 (
  2  deptno number references dept( deptno )
  3  );

Table created.

SQL> create table dept_child3 (
  2    dept_child3_no number primary key,
  3    deptno number references dept( deptno )
  4  );

Table created.

SQL> create table dept_grandchild (
  2    dept_child3_no number references dept_child3( dept_child3_no )
  3  );

Table created.

并验证查询是否返回了预期的输出

and verified that the query returned the expected output

SQL> ed
Wrote file afiedt.buf

  1  select parent, child, level from (
  2  select parent_table.table_name parent, child_table.table_name child
  3   from user_tables      parent_table,
  4        user_constraints parent_constraint,
  5        user_constraints child_constraint,
  6        user_tables      child_table
  7  where parent_table.table_name = parent_constraint.table_name
  8    and parent_constraint.constraint_type IN( 'P', 'U' )
  9    and child_constraint.r_constraint_name = parent_constraint.constraint_name
 10    and child_constraint.constraint_type   = 'R'
 11    and child_table.table_name = child_constraint.table_name
 12    and child_table.table_name != parent_table.table_name
 13  )
 14  start with parent = 'DEPT'
 15* connect by prior child = parent
SQL> /

PARENT                         CHILD                               LEVEL
------------------------------ ------------------------------ ----------
DEPT                           DEPT_CHILD3                             1
DEPT_CHILD3                    DEPT_GRANDCHILD                         2
DEPT                           DEPT_CHILD2                             1
DEPT                           EMP                                     1

这篇关于使用递归查询构建表依赖图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！