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

我有一个包含分层数据的表.目前这个层次结构中有~8个级别.

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

我有PL / SQL存储函数为我做这些查找,每个函数都有一个select * from tbl start with … connect by … statement.当我查询一些记录时这很好用,但我现在处于需要一次查询~10k记录并且每个记录运行此功能的情况.这需要2-3分钟,我需要它在几秒钟内运行.

根据我对当前数据的了解,使用一些启发式算法,我可以摆脱查找功能,只需要做childrecord.key || ‘％’LIKE parentrecord.key但这是一个非常脏的黑客,并不会一直有效.

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

我的想法是,我需要一个插入触发器,它基本上将通过查询运行连接,并且对于上一层次的匹配,它将在查找表中插入一条记录.为了处理旧数据,我只需要使用级联删除设置主表的外键.

有比这更好的选择吗？我错过了另一种可以更快地确定这些遥远的祖先/后代关系的方法吗？

编辑：这似乎正是我在想的：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查询和级联外键来维护这样的图表.太多的间接活动,太难以正确.还有一个物化视图,因为当我们删除ID = 4的源记录时,我们无法编写将删除1> 5记录的SQL查询.

所以我建议你读一篇由Dong,Libkin,Su和Wong撰写的名为Maintaining Transitive Closure of Graphs in SQL的论文.这包含了许多理论和一些粗糙的(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.因此,可能起作用取决于数据的复杂性.

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!