Oracle自连接性能问题：获取父序列号慢查询优化

1. 给核心字段建立复合索引 #

针对关联和过滤用到的字段创建复合索引，让数据库快速定位符合条件的记录，避免全表扫描：

CREATE INDEX idx_order_level_row ON your_table (PARENT_ORDER, CHILD_ORDER, PLAN_LEVEL, ROW_NUMBER);

如果父级的PLAN_LEVEL固定比子级小1，可调整索引优先级，把PLAN_LEVEL前置，同时包含ROW_NUMBER，进一步缩小检索范围。

2. 用递归CTE替代全量自关联 #

递归CTE会逐层遍历层级关系，每次只关联当前层级的父级数据，避免全表自关联带来的大量比对：

WITH RECURSIVE order_hierarchy AS (
    -- 锚点：最顶层订单（根据实际业务调整PLAN_LEVEL值）
    SELECT 
        ROW_NUMBER,
        PARENT_ORDER,
        CHILD_ORDER,
        PLAN_LEVEL,
        CHILD_ORDER AS CURRENT_ORDER,
        PARENT_ORDER AS PARENT_SERIAL
    FROM your_table
    WHERE PLAN_LEVEL = 1
    UNION ALL
    -- 递归关联下一层级
    SELECT 
        t.ROW_NUMBER,
        t.PARENT_ORDER,
        t.CHILD_ORDER,
        t.PLAN_LEVEL,
        t.CHILD_ORDER AS CURRENT_ORDER,
        oh.PARENT_SERIAL
    FROM your_table t
    JOIN order_hierarchy oh 
        ON t.PARENT_ORDER = oh.CURRENT_ORDER
        AND t.PLAN_LEVEL = oh.PLAN_LEVEL + 1
        AND t.ROW_NUMBER > oh.ROW_NUMBER
)
SELECT * FROM order_hierarchy;

3. 提前过滤无关数据 #

如果原查询包含大量不需要的记录，先通过WHERE条件筛选出目标数据，再进行关联操作，减少关联的数据量：

WITH filtered_data AS (
    SELECT ROW_NUMBER, PARENT_ORDER, CHILD_ORDER, PLAN_LEVEL
    FROM your_table
    -- 这里添加业务过滤条件，比如日期、订单类型等
    WHERE ORDER_DATE >= '2024-01-01'
)
SELECT t.*, t1.PARENT_ORDER AS PARENT_SERIAL
FROM filtered_data t
JOIN filtered_data t1
    ON t.PARENT_ORDER = t1.CHILD_ORDER
    AND t.PLAN_LEVEL = t1.PLAN_LEVEL + 1
    AND t.ROW_NUMBER > t1.ROW_NUMBER;

4. 优化ROW_NUMBER生成逻辑 #

如果ROW_NUMBER是通过ROW_NUMBER() OVER (ORDER BY ...)生成的，确保ORDER BY的字段存在索引，这样生成序号的速度更快，后续关联比对也更高效。

5. 用窗口函数直接匹配父级 #

如果父级与子级的PLAN_LEVEL连续，且同组内的ROW_NUMBER顺序对应层级关系，可使用LAG窗口函数替代自关联，直接获取父级序列号：

SELECT 
    *,
    LAG(PARENT_ORDER) OVER (
        PARTITION BY PARENT_ORDER, PLAN_LEVEL - 1 
        ORDER BY ROW_NUMBER
    ) AS PARENT_SERIAL
FROM your_table;

该方案无需自关联，性能提升明显，需根据业务场景确认PARTITION BY的分组逻辑是否准确。

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