Oracle SQL中对比同表不同RunId结果集列差异并输出变更列名的高性能解决方案问询

针对你提出的需求——对比同表中两个不同RunId的结果集，按ID匹配后输出值发生变化的列名，同时要兼顾百万级数据的性能，我整理了两种实用的解决方案，覆盖单SQL实现和高性能存储过程两种场景：

一、单SQL实现方案（适合列数固定或可动态适配的场景） #

如果你的列数量虽然多但相对固定，或者可以通过动态SQL生成UNPIVOT语句，单SQL就能搞定这个需求。核心思路是把横向的列转成纵向的行，再按ID和列名关联对比：

WITH result1 AS (
    SELECT id, name, address, age
    FROM Table1 
    WHERE runId = 1
),
result2 AS (
    SELECT id, name, address, age
    FROM Table1 
    WHERE runId = 2
),
-- 将结果集转成（id, columnName, columnValue）的格式
unpivoted1 AS (
    SELECT id, columnName, columnValue
    FROM result1
    UNPIVOT (
        columnValue FOR columnName IN (name, address, age)
    )
),
unpivoted2 AS (
    SELECT id, columnName, columnValue
    FROM result2
    UNPIVOT (
        columnValue FOR columnName IN (name, address, age)
    )
)
-- 关联两个行转列后的结果，筛选值不同的记录
SELECT u1.id, u1.columnName
FROM unpivoted1 u1
JOIN unpivoted2 u2 
    ON u1.id = u2.id 
    AND u1.columnName = u2.columnName
WHERE u1.columnValue <> u2.columnValue
-- 处理NULL值：如果列可能为NULL，用NVL统一替换避免误判
-- WHERE NVL(u1.columnValue, '<<NULL>>') <> NVL(u2.columnValue, '<<NULL>>')
ORDER BY u1.id, u1.columnName;

性能优化提示： #

• 给Table1创建复合索引：CREATE INDEX idx_table1_runid_id ON Table1(runId, id);，如果对比的列经常被查询，也可以做成覆盖索引：CREATE INDEX idx_table1_runid_id_cols ON Table1(runId, id, name, address, age);，减少回表开销。
• 如果列非常多，手动写UNPIVOT的IN列表太麻烦，可以用动态SQL生成这个语句（比如从USER_TAB_COLUMNS中获取列名）。

二、存储过程方案（适合超大数据量+并行处理场景） #

如果数据量达到数百万级，单SQL可能性能不足，这时可以用存储过程结合Oracle的并行处理能力来拆分任务，提升效率。下面是一个支持动态列对比+并行处理的示例：

CREATE OR REPLACE PROCEDURE compare_table1_runids(
    p_runid1 IN NUMBER,
    p_runid2 IN NUMBER,
    p_parallel_degree IN NUMBER DEFAULT 4
)
IS
    v_sql VARCHAR2(4000);
    v_col_list VARCHAR2(2000);
BEGIN
    -- 动态获取需要对比的列名（排除id和runId）
    SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id)
    INTO v_col_list
    FROM user_tab_columns
    WHERE table_name = 'TABLE1'
    AND column_name NOT IN ('ID', 'RUNID');

    -- 生成动态对比SQL，使用并行提示
    v_sql := '
        WITH result1 AS (
            SELECT id, ' || v_col_list || '
            FROM Table1 
            WHERE runId = :p1
        ),
        result2 AS (
            SELECT id, ' || v_col_list || '
            FROM Table1 
            WHERE runId = :p2
        ),
        unpivoted1 AS (
            SELECT id, columnName, columnValue
            FROM result1
            UNPIVOT (
                columnValue FOR columnName IN (' || v_col_list || ')
            )
        ),
        unpivoted2 AS (
            SELECT id, columnName, columnValue
            FROM result2
            UNPIVOT (
                columnValue FOR columnName IN (' || v_col_list || ')
            )
        )
        SELECT /*+ PARALLEL(' || p_parallel_degree || ') */
               u1.id, u1.columnName
        FROM unpivoted1 u1
        JOIN unpivoted2 u2 
            ON u1.id = u2.id 
            AND u1.columnName = u2.columnName
        WHERE NVL(u1.columnValue, ''<<NULL>>'') <> NVL(u2.columnValue, ''<<NULL>>'')
        ORDER BY u1.id, u1.columnName';

    -- 执行动态SQL并输出结果（可以插入临时表或者直接返回结果集）
    -- 这里用DBMS_OUTPUT示例，实际可以用REF CURSOR返回
    FOR rec IN EXECUTE IMMEDIATE v_sql USING p_runid1, p_runid2 LOOP
        DBMS_OUTPUT.PUT_LINE('id: ' || rec.id || ', columnName: ' || rec.columnName);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

并行处理进阶优化： #

如果数据量极大，可以用DBMS_PARALLEL_EXECUTE包将数据按ID范围拆分成多个任务，并行执行对比逻辑，避免单进程处理瓶颈。大致步骤是：

1. 创建任务并拆分ID范围；
2. 定义每个子任务的对比逻辑；
3. 启动并行执行；
4. 汇总结果。

验证示例 #

用你给出的测试数据，执行上述单SQL或存储过程，会得到如下输出：

id  columnName
1   name
1   age
2   age

备注：内容来源于stack exchange，提问作者Shanij P.S.