Oracle双向关联表全关联对象查询问题及优化方案

我有一张通过单条记录实现双向绑定的关联表，比如表中id=5的记录（object_id=8、connected_object_id=4）就表示对象8和4互相关联。表结构及数据如下：

需求是查询与指定对象直接或间接关联的所有对象，比如查询对象12时，期望返回表中所有记录（因为12关联2，2关联1、4，4关联1、5、8，5关联1，所有对象都连通）。

初始查询的问题 #

最初使用的递归查询语句如下：

Select * from TABLE t
START WITH t.object_id = xxx
CONNECT BY NOCYCLE PRIOR t.object_id = t.object_id
OR PRIOR t.connected_object_id = t.connected_object_id
OR PRIOR t.object_id = t.connected_object_id
OR PRIOR t.connected_object_id = t.object_id

该语句存在两个缺陷：

• 当目标对象仅出现在connected_object_id列时，查询无法匹配到关联记录
• 部分场景下会出现查询挂起的问题

优化后的查询语句 #

在Abdul Alim Shakir协助下，得到了可以解决上述问题的最终查询语句：

WITH all_links(source, target) AS (
SELECT object_id, connected_object_id FROM connections
UNION
SELECT connected_object_id, object_id FROM connections
),
connected(object_id) AS (
SELECT 12 FROM DUAL
UNION ALL
SELECT source from all_links START WITH source = 12
       CONNECT BY NOCYCLE PRIOR source = target
)
SELECT DISTINCT c.*
FROM connections c
JOIN connected co
ON c.object_id = co.object_id OR c.connected_object_id = co.object_id;

语句逻辑说明 #

1. all_links CTE：将原表中的单向关联转换为双向关联，确保不管对象出现在object_id还是connected_object_id列，都能被递归遍历到
2. connected CTE：通过递归查询，找出与指定对象（示例中为12）直接或间接关联的所有对象
3. 最终查询：将原表与connected结果关联，筛选出所有关联记录并去重，得到完整的关联数据

内容的提问来源于stack exchange，提问作者L.dev