Oracle中两个游标结果逐行对比出现笛卡尔积问题求助

问题本质 #

你当前的代码用了嵌套循环：外层遍历本地库的表，内层每次都完整遍历远程库的所有表，导致本地每张表都和远程所有表做对比，自然产生笛卡尔积。

解决思路 #

先把本地和远程的目标表统计数据分别存入字符串索引的关联数组，键用owner.table_name确保表能精准匹配，再逐一对比对应表的统计值。这种方式避免了嵌套循环，还能检测两边存在差异的表（一方有另一方没有）。

修改后的代码 #

SET SERVEROUTPUT ON;

DECLARE
    -- 定义存储表统计信息的记录类型
    TYPE table_stats_rec IS RECORD (
        owner            VARCHAR2(40),
        table_name       VARCHAR2(40),
        column_name      VARCHAR2(40),
        count_rows       NUMBER,
        max_primary_key  VARCHAR2(4000),
        min_primary_key  VARCHAR2(4000),
        sum_primary_key  VARCHAR2(4000)
    );
    -- 定义关联数组类型，用owner.table_name作为索引
    TYPE table_stats_tab IS TABLE OF table_stats_rec INDEX BY VARCHAR2(81);
    
    v_local_stats   table_stats_tab;  -- 本地库统计数据
    v_remote_stats  table_stats_tab; -- 远程库统计数据
    v_diff_count    NUMBER;
    v_table_key     VARCHAR2(81);
BEGIN
    -- 收集本地库所有带主键的表统计信息
    FOR rec IN (
        SELECT cons.owner, cols.table_name, cols.column_name
        FROM all_constraints cons
        JOIN all_cons_columns cols 
            ON cons.constraint_name = cols.constraint_name 
            AND cons.owner = cols.owner
        JOIN all_tables tab 
            ON cols.table_name = tab.table_name
        JOIN all_tab_columns tab_col 
            ON cols.table_name = tab_col.table_name 
            AND cols.column_name = tab_col.column_name
        WHERE cons.constraint_type = 'P'
          AND cols.position = 1
          AND tab.table_name NOT LIKE '%_MV'
        ORDER BY cons.owner, cols.table_name
    ) LOOP
        v_table_key := rec.owner || '.' || rec.table_name;
        -- 动态SQL获取统计值
        EXECUTE IMMEDIATE '
            SELECT COUNT(*),
                   max(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || '),
                   min(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || '),
                   SUM(CASE WHEN REGEXP_LIKE(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || ',''^[+-]?\d*\.?\d+$'') 
                           THEN TO_NUMBER(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || ') 
                           ELSE 0 END)
            FROM ' || DBMS_ASSERT.ENQUOTE_NAME(rec.owner, FALSE) || '.' || DBMS_ASSERT.ENQUOTE_NAME(rec.table_name, FALSE)
        INTO v_local_stats(v_table_key).count_rows,
             v_local_stats(v_table_key).max_primary_key,
             v_local_stats(v_table_key).min_primary_key,
             v_local_stats(v_table_key).sum_primary_key;
        -- 补充基本信息
        v_local_stats(v_table_key).owner := rec.owner;
        v_local_stats(v_table_key).table_name := rec.table_name;
        v_local_stats(v_table_key).column_name := rec.column_name;
    END LOOP;

    -- 收集远程库所有带主键的表统计信息
    FOR rec IN (
        SELECT cons.owner, cols.table_name, cols.column_name
        FROM all_constraints@INST2 cons
        JOIN all_cons_columns@INST2 cols 
            ON cons.constraint_name = cols.constraint_name 
            AND cons.owner = cols.owner
        JOIN all_tables@INST2 tab 
            ON cols.table_name = tab.table_name
        JOIN all_tab_columns@INST2 tab_col 
            ON cols.table_name = tab_col.table_name 
            AND cols.column_name = tab_col.column_name
        WHERE cons.constraint_type = 'P'
          AND cols.position = 1
          AND tab.table_name NOT LIKE '%_MV'
        ORDER BY cons.owner, cols.table_name
    ) LOOP
        v_table_key := rec.owner || '.' || rec.table_name;
        -- 动态SQL获取远程表统计值
        EXECUTE IMMEDIATE '
            SELECT COUNT(*),
                   max(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || '),
                   min(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || '),
                   SUM(CASE WHEN REGEXP_LIKE(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || ',''^[+-]?\d*\.?\d+$'') 
                           THEN TO_NUMBER(' || DBMS_ASSERT.ENQUOTE_NAME(rec.column_name, FALSE) || ') 
                           ELSE 0 END)
            FROM ' || DBMS_ASSERT.ENQUOTE_NAME(rec.owner, FALSE) || '.' || DBMS_ASSERT.ENQUOTE_NAME(rec.table_name, FALSE) || '@INST2'
        INTO v_remote_stats(v_table_key).count_rows,
             v_remote_stats(v_table_key).max_primary_key,
             v_remote_stats(v_table_key).min_primary_key,
             v_remote_stats(v_table_key).sum_primary_key;
        -- 补充基本信息
        v_remote_stats(v_table_key).owner := rec.owner;
        v_remote_stats(v_table_key).table_name := rec.table_name;
        v_remote_stats(v_table_key).column_name := rec.column_name;
    END LOOP;

    -- 对比本地和远程的表统计差异
    v_table_key := v_local_stats.FIRST;
    WHILE v_table_key IS NOT NULL LOOP
        IF v_remote_stats.EXISTS(v_table_key) THEN
            v_diff_count := v_local_stats(v_table_key).count_rows - v_remote_stats(v_table_key).count_rows;
            IF v_diff_count <> 0 THEN
                DBMS_OUTPUT.PUT_LINE(
                    '表: ' || v_local_stats(v_table_key).owner || '.' || v_local_stats(v_table_key).table_name || CHR(10) ||
                    '主键列: ' || v_local_stats(v_table_key).column_name || CHR(10) ||
                    '最大主键值: ' || v_local_stats(v_table_key).max_primary_key || ' / ' || v_remote_stats(v_table_key).max_primary_key || CHR(10) ||
                    '最小主键值: ' || v_local_stats(v_table_key).min_primary_key || ' / ' || v_remote_stats(v_table_key).min_primary_key || CHR(10) ||
                    '主键数值总和: ' || v_local_stats(v_table_key).sum_primary_key || ' / ' || v_remote_stats(v_table_key).sum_primary_key || CHR(10) ||
                    '记录数: ' || v_local_stats(v_table_key).count_rows || ' / ' || v_remote_stats(v_table_key).count_rows || CHR(10) ||
                    '记录数差异: ' || v_diff_count || CHR(10) || '-------------------------'
                );
            END IF;
        ELSE
            DBMS_OUTPUT.PUT_LINE('远程库不存在表: ' || v_local_stats(v_table_key).owner || '.' || v_local_stats(v_table_key).table_name);
        END IF;
        v_table_key := v_local_stats.NEXT(v_table_key);
    END LOOP;

    -- 检查远程库有但本地没有的表
    v_table_key := v_remote_stats.FIRST;
    WHILE v_table_key IS NOT NULL LOOP
        IF NOT v_local_stats.EXISTS(v_table_key) THEN
            DBMS_OUTPUT.PUT_LINE('本地库不存在表: ' || v_remote_stats(v_table_key).owner || '.' || v_remote_stats(v_table_key).table_name);
        END IF;
        v_table_key := v_remote_stats.NEXT(v_table_key);
    END LOOP;
END;
/

核心改进 #

• 用owner.table_name作为关联数组的索引，确保本地和远程的同一张表能精准匹配
• 分开收集两边数据再对比，彻底避免嵌套循环导致的笛卡尔积
• 新增了表存在性检测，覆盖“一方有表另一方没有”的场景

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