Oracle递归查询优化：避免重复层级遍历问题

表结构与问题场景 #

现有NAMES表结构及数据如下：

使用以下递归CTE查询时，会出现重复结果（原语句中T_2为笔误，已修正为N_2）：

WITH rec as
(
 SELECT N_1.PROPERTY, N_1.NAME, N_1.REFERENCE 
 FROM NAMES N_1
 WHERE PROPERTY = 0
 UNION ALL
 SELECT N_2.PROPERTY, N_2.NAME, N_2.REFERENCE  
 FROM NAMES N_2
 JOIN rec r ON N_2.PROPERTY = r.REFERENCE
)
SELECT NAME FROM rec;

问题：从Mike出发，John、James、Robert都指向PROPERTY=4，导致递归时会三次遍历Michael，进而重复返回David和Mark各三次，最终结果存在大量重复。

虽然可以在最终查询时加DISTINCT过滤：

SELECT DISTINCT NAME FROM rec;

但这种方式是先查询出所有重复记录再过滤，在复杂大表场景下会消耗大量资源，效率低下。且Oracle不支持在递归分支的UNION ALL后直接加DISTINCT。

解决方案：递归过程中避免重复遍历节点 #

可以通过在递归逻辑中维护已访问节点的集合，让数据库只处理未访问过的节点，从根源上避免重复遍历。

方法1：使用集合记录已访问的PROPERTY #

方式A：使用Oracle自带类型（无需额外创建）

利用Oracle内置的SYS.ODCINUMBERLIST数字列表类型，在递归CTE中跟踪已访问的PROPERTY，每次只处理未访问过的节点：

WITH rec AS (
    SELECT 
        N_1.PROPERTY, 
        N_1.NAME, 
        N_1.REFERENCE,
        SYS.ODCINUMBERLIST(N_1.PROPERTY) AS visited
    FROM NAMES N_1
    WHERE PROPERTY = 0
    UNION ALL
    SELECT 
        N_2.PROPERTY, 
        N_2.NAME, 
        N_2.REFERENCE,
        r.visited MULTISET UNION SYS.ODCINUMBERLIST(N_2.PROPERTY) AS visited
    FROM NAMES N_2
    JOIN rec r ON N_2.PROPERTY = r.REFERENCE
    WHERE NOT EXISTS (
        SELECT 1 
        FROM TABLE(r.visited) v 
        WHERE v.COLUMN_VALUE = N_2.PROPERTY
    )
)
SELECT NAME FROM rec;

方式B：自定义嵌套表类型

如果需要更灵活的类型定义，可以先创建全局嵌套表类型：

CREATE OR REPLACE TYPE number_list AS TABLE OF NUMBER;
/

再使用该类型实现递归去重：

WITH rec AS (
    SELECT 
        N_1.PROPERTY, 
        N_1.NAME, 
        N_1.REFERENCE,
        number_list(N_1.PROPERTY) AS visited
    FROM NAMES N_1
    WHERE PROPERTY = 0
    UNION ALL
    SELECT 
        N_2.PROPERTY, 
        N_2.NAME, 
        N_2.REFERENCE,
        r.visited MULTISET UNION number_list(N_2.PROPERTY) AS visited
    FROM NAMES N_2
    JOIN rec r ON N_2.PROPERTY = r.REFERENCE
    WHERE NOT EXISTS (
        SELECT 1 
        FROM TABLE(r.visited) v 
        WHERE v.COLUMN_VALUE = N_2.PROPERTY
    )
)
SELECT NAME FROM rec;

方法2：使用CONNECT BY层次查询（更简洁） #

Oracle的CONNECT BY语法可以结合NOCYCLE自动优化遍历路径，避免重复处理同一节点：

SELECT DISTINCT NAME
FROM NAMES
START WITH PROPERTY = 0
CONNECT BY NOCYCLE PRIOR REFERENCE = PROPERTY;

其中NOCYCLE用于防止潜在的循环引用，CONNECT BY PRIOR REFERENCE = PROPERTY定义了层级关联关系，这里的DISTINCT效率远高于递归CTE后过滤，因为层次查询会直接跳过已访问的节点。

验证结果 #

两种方法都会返回无重复的期望结果：

内容的提问来源于stack exchange，提问作者Ultra_Igor