以分层方式创建展平的表格/视图
我有一张包含分层数据的表格。 目前在这个层次中有〜8个层次。
我非常喜欢这些数据的结构，但是当我需要知道8级记录是否是1级记录的孩子时，性能很糟糕。
我有PL / SQL存储函数，为我做这些查询，每个都有一个select * from tbl start with ... connect by...语句。 当我查询一些记录时，这很好，但我现在处于一种情况，我需要一次查询〜10k条记录，并且每个记录都运行此函数。 这需要2-3分钟，我需要它在几秒钟内运行。
根据我对当前数据的了解，使用一些启发式，我可以摆脱查找功能，只是做childrecord.key || '%' LIKE parentrecord.key childrecord.key || '%' LIKE parentrecord.key但这是一个非常肮脏的黑客，并不总是工作。
所以现在我正在考虑对于这个层次定义的表，我需要一个单独的父 - 子表，它将包含每个关系...对于从1-8级开始的层次结构，将会有8个！ 记录，将1与2，1与3，...，1与8和2与3，2与4，...，2与8相关联。等等。
我的想法是，我需要一个插入触发器，它将基本上运行connect by查询connect by并且对于层次结构中的每个匹配项，它都会在查找表中插入一条记录。 而为了处理旧数据，我只需将级联删除的外键设置到主表。
有没有比这更好的选择？ 我是否错过了另一种可以更快地确定这些遥远的祖先/后代关系的方式？
编辑：这似乎正是我在想什么：http://evolt.org/working_with_hierarchical_data_in_sql_using_ancestor_tables
所以你想要的是实现传递闭包。 也就是说，鉴于这个应用程序表...
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查询，当我们删除ID=4的源记录时，这个SQL查询将打碎1->5记录。
所以我建议你阅读一篇名为“维护SQL中图的传递闭包”的论文，由Dong，Libkin，Su和Wong撰写。 这包含了许多理论和一些粗糙的（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 。 那么，可能会发挥什么取决于数据的复杂性。