我真的很喜欢数据结构的方式,但是当我需要知道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.因此,可能起作用取决于数据的复杂性.
