为分层定义的数据集创建展平的表/视图

Question

我有一个包含层次结构数据的表。目前，在此层次结构中大约有8个级别。

我真的很喜欢数据的结构方式，但是当我需要知道第8级记录是否是第1级记录的子级时，性能令人沮丧。

我有PL / SQL存储的函数，这些函数为我执行这些查找，每个函数都有一条select * from tbl start with ... connect by...语句。当我查询少量记录时，这种方法很好用，但是现在我需要一次查询约1万条记录，并且每个记录都运行此功能。我需要2-3分钟才能在几秒钟内运行它。

根据我对当前数据的了解，使用一些启发式方法，我可以摆脱查找功能而只能这样做，childrecord.key || '%' LIKE parentrecord.key但这是一个非常肮脏的技巧，并且并不总是有效。

所以现在我在想，对于此层次结构定义的表，我需要有一个单独的父子表，该表将包含每个关系…对于从1-8级开始的层次结构，将有8个！记录，将1与2、1与3，…，1与8和2与3、2与4，…，2与8相关联。依此类推。

我的想法是，我需要有一个插入触发器，该触发器将基本上在其中运行connect by查询，并且对于每次进行匹配的层次结构，都将在查找表中插入一条记录。为了处理旧数据，我将使用级联删除将主键设置为主表。

有没有比这更好的选择了？我是否错过了另一种可以更快地确定这些远祖/后裔关系的方式？

编辑：
这似乎正是我在想什么：http
:
//evolt.org/working_with_hierarchical_data_in_sql_using_ancestor_tables

Answer 1

因此，您想要的是实现可传递闭包。也就是说，给定此应用程序表…

 ID   | PARENT_ID
------+----------
    1 | 
    2 |         1
    3 |         2
    4 |         2
    5 |         4

…图形表如下所示：

 PARENT_ID | CHILD_ID
-----------+----------
         1 |        2
         1 |        3
         1 |        4
         1 |        5
         2 |        3
         2 |        4
         2 |        5
         4 |        5

在Oracle中可以维护一个这样的表，尽管您需要为其滚动自己的框架。问题是这是否值得开销。如果源表是易失性的，则保持图形数据新鲜可能花费的周期比您将在查询中节省的周期更多。只有您知道数据的个人资料。

我认为您无法使用CONNECT
BY查询和级联外键来维护这样的图形表。间接活动过多，难以正确解决。物化视图也已退出，因为我们无法编写SQL查询，1->5当删除的源记录时，该查询将破坏记录ID=4。

因此，我建议您阅读由Dong，Libkin，Su和Wong撰写的名为“维护SQL中的图的传递闭包”的论文。它包含许多理论和一些粗糙的（Oracle）SQL，但是它将为您构建维护图表所需的PL /
SQL提供基础。

“您能否扩展一下用CONNECT BY
/级联FK维护起来太困难的部分？如果我控制对表的访问，并且所有插入/更新/删除都是通过存储过程进行的，那么在哪种情况下会发生这种情况？分解？”

考虑1->5是短路的记录1->2->4->5。现在，如果像我之前说的那样，我们删除的源记录，该ID=4怎么办？级联外键可以删除该条目2->4和4->5。但是，尽管它们
不再代表 图形中 的有效边，但仍保留 在图形表中1->5（并且确实如此2->5）。 __

可能有效的方法（我想，我还没有做到）是在源表中使用附加的合成键，如下所示。

 ID   | PARENT_ID | NEW_KEY
------+-----------+---------
    1 |           | AAA
    2 |         1 | BBB
    3 |         2 | CCC
    4 |         2 | DDD
    5 |         4 | EEE

现在，图形表将如下所示：

 PARENT_ID | CHILD_ID | NEW_KEY
-----------+----------+---------
         1 |        2 | BBB
         1 |        3 | CCC
         1 |        4 | DDD
         1 |        5 | DDD
         2 |        3 | CCC
         2 |        4 | DDD
         2 |        5 | DDD
         4 |        5 | DDD

因此，图形表具有一个外键，该外键引用生成它的源表中的关系，而不是链接到ID。然后删除的记录，ID=4将会级联删除图形表中的所有记录，其中NEW_KEY=DDD。

如果任何给定的ID只能有零个或一个父ID，这将起作用。但是，如果允许发生这种情况，它将无法正常工作：

 ID   | PARENT_ID
------+----------
    5 |         2
    5 |         4

换句话说，边缘1->5代表1->2->4->5和1->2->5。因此，可能有效的方法取决于数据的复杂性。