Oracle跨库比对结果集并删除冗余记录的实现方法

Alright, let's work through this sync problem. The key challenge here is that we have duplicate records with identical values across two databases, so MINUS won't catch the extra rows (since it only checks for value differences), and ROWID is useless across database links. Here's a solid approach to fix this:

Core Idea #

We'll use row numbering within duplicate groups to distinguish identical rows, then compare how many rows each group should have (from DB1) versus how many exist (in DB2). Any rows in DB2 that exceed the count from DB1 get deleted.

Step 1: Define Expected Row Counts from DB1 #

First, we'll calculate how many rows each (c1, c2) group should have by generating row numbers for DB1's t1 table, then taking the maximum row number per group:

SELECT c1, c2, MAX(rn) AS allowed_row_count
FROM (
    SELECT c1, c2,
           ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1, c2) AS rn
    FROM db1.t1
) t1_grouped
GROUP BY c1, c2;

For your sample data, this returns:

Step 2: Identify Redundant Rows in DB2 #

Next, we'll generate row numbers for DB2's t2 table (via dblink) and match them against the allowed counts from DB1. We'll also capture each row's ROWID (valid within DB2) to precisely target deletions:

-- First, test this SELECT to confirm which rows will be deleted
SELECT t2_rn.rid
FROM (
    SELECT c1, c2,
           ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1, c2) AS rn,
           ROWID AS rid
    FROM t2@db2_link -- Replace with your actual dblink name
) t2_rn
LEFT JOIN (
    SELECT c1, c2, MAX(rn) AS allowed_row_count
    FROM (
        SELECT c1, c2,
               ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1, c2) AS rn
        FROM db1.t1
    ) t1_grouped
    GROUP BY c1, c2
) t1_counts ON t2_rn.c1 = t1_counts.c1 AND t2_rn.c2 = t1_counts.c2
WHERE 
    -- Delete rows that exceed the allowed count for their group
    t2_rn.rn > t1_counts.allowed_row_count
    -- Also delete any groups that exist in DB2 but not in DB1
    OR t1_counts.allowed_row_count IS NULL;

For your sample DB2 data, this will return the ROWIDs of 3 redundant rows (2 extra A rows and 1 extra B row).

Step 3: Execute the Delete #

Once you've verified the SELECT returns the correct rows, convert it to a DELETE statement:

DELETE FROM t2@db2_link
WHERE ROWID IN (
    SELECT t2_rn.rid
    FROM (
        SELECT c1, c2,
               ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1, c2) AS rn,
               ROWID AS rid
        FROM t2@db2_link
    ) t2_rn
    LEFT JOIN (
        SELECT c1, c2, MAX(rn) AS allowed_row_count
        FROM (
            SELECT c1, c2,
                   ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1, c2) AS rn
            FROM db1.t1
        ) t1_grouped
        GROUP BY c1, c2
    ) t1_counts ON t2_rn.c1 = t1_counts.c1 AND t2_rn.c2 = t1_counts.c2
    WHERE 
        t2_rn.rn > t1_counts.allowed_row_count
        OR t1_counts.allowed_row_count IS NULL
);

Important Notes #

• Always test first: Run the SELECT version before deleting to avoid accidental data loss.
• Performance for large tables: If t2 is very large, consider creating a temporary table in DB2 with the allowed counts (via dblink) to speed up the join.
• Date format consistency: Ensure the c2 date field is formatted consistently across both databases. If not, use TO_DATE(c2, 'MM/DD/YYYY') in both subqueries to standardize.
• Permissions: Make sure your dblink user has DELETE privileges on DB2's t2 table.

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