sql - 创建分层定义的数据集的扁平表/ View

Question

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

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

我有 PL/SQL 存储函数来为我执行这些查找，每个函数都有一个 select * from tbl start with ... connect by... 语句。当我查询少量记录时，这工作得很好，但我现在的情况是，我需要一次查询约 10k 条记录，并为每条记录运行此函数。它需要 2-3 分钟，而我需要它在几秒钟内运行。

根据我对当前数据的了解，使用一些启发式方法，我可以摆脱查找功能，只需执行 childrecord.key || '%' 就像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 查询和级联外键来维护这样的图表。间接事件太多，很难做好。此外，物化 View 也已过时，因为我们无法编写 SQL 查询来消除 1->5当我们删除 ID=4 的源记录时记录.

所以我建议你阅读一篇名为 Maintaining Transitive Closure of Graphs in SQL 的论文作者:Dong、Libkin、Su 和 Wong。这包含大量理论和一些粗糙的 (Oracle) SQL，但它将为您提供构建维护图形表所需的 PL/SQL 的基础。

<小时/>

"can you expand on the part about it being too difficult to maintain with CONNECT BY/cascading FKs? If I control access to the table and all inserts/updates/deletes take place via stored procedures, what kinds of scenarios are there where this would break down?"

考虑记录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 。因此，什么可能有效取决于数据的复杂性。