Compare and sync tables: Jonathan Lewis and DBMS_RECTIFIER_DIFF

This is the first in a series of posts on comparing data and synchronizing tables. Jonathan Lewis asked for a volunteer to complete his blog post on dbms_rectifier_diff, so I’ll start with that. He has explained briefly the history of the package. I am going to present and test the 12c version.

Test Data

I use two tables called T_SOURCE and T_TARGET, with a primary key on KEY_NUM and KEY_DATE and two other columns called VAL_TS (timestamp) and VAL_STRING. There are 300 rows in each table and 30 differences in all. The details are here > Compare and sync tables: test data

Here are a few lines from each table:

TABLE_NAME KEY_NUM KEY_DATE VAL_TS VAL_STRING
T_SOURCE 1 31-JAN-14 31-JAN-14 06.45.44 PM INSERT – in source, not in target ****************
T_TARGET 11 31-JAN-14 31-JAN-14 06.45.44 PM DELETE – not in source, in target ****************
T_SOURCE 21 31-JAN-14 31-JAN-14 06.45.44 PM UPDATE – put this in target **********************
T_TARGET 21 31-JAN-14 31-JAN-14 06.45.44 PM UPDATE – update this from source *****************
T_SOURCE 31 31-JAN-14 31-JAN-14 06.45.44 PM ZERO – same in source and target *****************
T_TARGET 31 31-JAN-14 31-JAN-14 06.45.44 PM ZERO – same in source and target *****************

Before Comparing

Update: there is a decent introduction to dbms_rectifier_diff here > Determining Differences Between Replicated Tables.

The package is documented in the Advanced Replication Management API Reference. Before using it, we need to create two tables: one to hold the rows that are different and one to say which row comes from which table. The first row has the same structure as the two original tables:

create table diff_data as
select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
from t_source
where 1=0;

This creates an empty table with the same structure. The second table has a structure specified in the description of the “missing_rows_oname2″ parameter: “The R_ID column shows the rowid of the row in the missing_rows_oname1 table, the PRESENT column shows the name of the site where the row is present, and the ABSENT column shows name of the site from which the row is absent.”

create table diff_rowids(
  present varchar2(128),
  absent varchar2(128),
  r_id rowid
);

Finally, I have to create a DATABASE LINK to my own database. We’ll see why in a minute.

create database link loopback connect to stew identified by stew using 'orcl';

Comparing with the differences procedure

set timing on
begin
dbms_rectifier_diff.differences(
  sname1 => user,         -- source schema
  oname1 => 'T_SOURCE',   -- source table
  reference_site => null, -- source dblink (null means here)
  sname2 => user,         -- target schema
  oname2 => 'T_TARGET',   -- target table
  comparison_site => 'LOOPBACK', -- target dblink (null means here)
  where_clause => null, -- optional where clause to filter some lines
  missing_rows_sname => user, -- output schema,
  missing_rows_oname1 => 'DIFF_DATA', -- table to list mismatching data
  missing_rows_oname2 => 'DIFF_ROWIDS', -- table to locate mismatching data
  missing_rows_site => null, -- output dblink (null means here)
  max_missing => 9999,    -- how many differences before stopping
  commit_rows => 9999     -- how many rows to commit in output
);
end;
/
anonymous block completed
Elapsed: 00:00:10.809

Update 2014-08-08: If I run this code two times in a row, the second time it takes “only” 1.6 seconds.

Over 10 seconds to compare two 300-line tables! Let’s see if it worked at least:

select a.present, a.absent, b.val_string, count(*) cnt
from diff_rowids a
join diff_data b on a.r_id = b.rowid
group by a.present, a.absent, b.val_string
order by b.val_string, a.present
PRESENT ABSENT VAL_STRING CNT
LOOPBACK.LOCALDOMAIN ORCL.LOCALDOMAIN DELETE – not in source, in target **************** 10
ORCL.LOCALDOMAIN LOOPBACK.LOCALDOMAIN INSERT – in source, not in target **************** 10
ORCL.LOCALDOMAIN LOOPBACK.LOCALDOMAIN UPDATE – put this in target ********************** 10
LOOPBACK.LOCALDOMAIN ORCL.LOCALDOMAIN UPDATE – update this from source ***************** 10


The result is correct: 10 rows to insert, 10 rows to delete, and 10 rows in each table that are different and require an update.

Synchronizing with the rectify procedure

begin
dbms_rectifier_diff.rectify(
  sname1 => user,         -- source schema
  oname1 => 'T_SOURCE',   -- source table
  reference_site => null, -- source dblink (null means here)
  sname2 => user,         -- target schema
  oname2 => 'T_TARGET',   -- target table
  comparison_site => 'LOOPBACK', -- target dblink (null means here)
  column_list => null, -- e.g. 'id,padding' , null=> all,
  -- leave no spaces around commas
  missing_rows_sname => user, -- output schema,
  missing_rows_oname1 => 'DIFF_DATA', -- table to list mismatching data
  missing_rows_oname2 => 'DIFF_ROWIDS',-- table to locate mismatching data
  missing_rows_site => null, -- output site (null => current)
  commit_rows => 9999 -- how many rows to commit in output
);
end;
/

Checking the result, the tables are synchronized:

select 't_source' table_name, count(*) cnt, val_string
from t_source
group by 't_source', val_string
union all
select 't_target', count(*), val_string
from t_target
group by 't_target', val_string
order by 3,1;
TABLE_NAME CNT VAL_STRING
t_source 10 INSERT – in source, not in target ****************
t_target 10 INSERT – in source, not in target ****************
t_source 10 UPDATE – put this in target **********************
t_target 10 UPDATE – put this in target **********************
t_source 280 ZERO – same in source and target *****************
t_target 280 ZERO – same in source and target *****************

Analyzing the differences procedure

First of all, why 10 seconds? It seems that going through the DBLINK makes a difference; if I run the same procedure without it, it only takes 3 seconds. Unfortunately, without the DBLINK the procedure is useless, because the PRESENT and ABSENT columns will both have the same value!

Using SQL trace and TKPROF, we can see that the procedure is generating and executing some PL/SQL. Here is the first block:

DECLARE
  row_count BINARY_INTEGER := 0;
  missing_rows BINARY_INTEGER := 0;
  arowid ROWID;
  CURSOR c IS
    SELECT KEY_DATE, KEY_NUM, VAL_STRING, VAL_TS
    FROM T_SOURCE
    MINUS
    SELECT KEY_DATE, KEY_NUM, VAL_STRING, VAL_TS
    FROM T_TARGET@LOOPBACK.LOCALDOMAIN;
BEGIN
  FOR r IN c LOOP
    missing_rows := missing_rows + 1;
    IF missing_rows > 9999 THEN
      COMMIT;
      EXIT;
    END IF;

    INSERT INTO DIFF_DATA (KEY_DATE, KEY_NUM, VAL_STRING, VAL_TS)
    VALUES (r.KEY_DATE, r.KEY_NUM, r.VAL_STRING, r.VAL_TS)
    returning rowid into arowid;

    SELECT ROWID INTO arowid
    FROM DIFF_DATA
    WHERE (KEY_DATE = r.KEY_DATE) AND (KEY_NUM = r.KEY_NUM)
    AND (VAL_STRING = r.VAL_STRING OR(VAL_STRING IS NULL AND r.VAL_STRING IS NULL))
    AND (VAL_TS = r.VAL_TS OR(VAL_TS IS NULL AND r.VAL_TS IS NULL)) ;

    INSERT INTO DIFF_ROWIDS (present, absent, r_id)
    VALUES('ORCL.LOCALDOMAIN', 'LOOPBACK.LOCALDOMAIN', arowid);
    row_count := row_count + 1;
    IF row_count >= 9999 THEN
      COMMIT;
      row_count := 0;
    END IF;
  END LOOP;
  COMMIT;
END;
/

I’ve cleaned this up and shortened it a bit to make it more legible. Basically, the cursor finds those rows in T_SOURCE that are not in T_TARGET, then inserts them into DIFF_DATA. After every insert, it gets the ROWID from the new DIFF_DATA row and inserts it into DIFF_ROWID.

This code may have been all right when the procedure was first written, but as of today it is horrible – or as Jonathan said, “dire”.

  • The INSERT into DIFF_DATA is done row by row. It should be done using FORALL. This means doing the first SELECT using an explicit cursor with BULK COLLECT and the LIMIT clause.
  • The SELECT to get the rowid is unnecessary. The FORALL…INSERT can use the RETURNING clause to return all the rowids at once.
  • The INSERT into DIFF_ROWID can then be done using FORALL.

This is “row_by_row” = “slow-by-slow” processing at its worst. Every row found means three SQL statements, including a SELECT to get one row from a table that is probably not indexed! Not only that, but the tables involved may be remote, which means a network round trip every time.

That’s the good news. The bad news is we’re only half done. Another PL/SQL block is now executed to handle the rows in T_TARGET that are not in T_SOURCE.

Frankly, I don’t understand why this isn’t just four SQL statements:

insert into diff_data
SELECT KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
FROM T_SOURCE
MINUS
SELECT KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
FROM T_TARGET@LOOPBACK.LOCALDOMAIN;

insert into diff_rowids
select 'ORCL.LOCALDOMAIN', 'LOOPBACK.LOCALDOMAIN', rowid
from diff_data;

insert into diff_data
SELECT KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
FROM T_TARGET
MINUS
SELECT KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
FROM T_SOURCE@LOOPBACK.LOCALDOMAIN;

insert into diff_rowids
select 'LOOPBACK.LOCALDOMAIN','ORCL.LOCALDOMAIN', rowid
from diff_data
where rowid not in (select r_id from diff_rowids);

I’m not going to bother analyzing the RECTIFY procedure. I’ve had enough.

What else is wrong?

Just for fun, since I have 12c I’ll make one of the columns “invisible”. This seems to work all right.

What about 11gR2 “virtual” columns? These don’t break the DIFFERENCES procedure, but they do break the RECTIFY procedure. Specifying the column_list parameter doesn’t appear to work: the procedure still tries to update the virtual column.

So, do you still want to compare some tables?

In his blog post, Jonathan said: “perhaps this script will be a pointer to a good enough solution for people facing the need to resynchronise data sets occasionally…It was a discussion of this type of problem at OpenWorld 2013 – and a comment about a licensed option using the package dbms_comparison – that prompted me to resurrect this very old script.”

Is this solution “good enough”? I have to say no. There is some good news, though, concerning dbms_comparison: the package itself is not licensed according to “my” Oracle sales team. The confusion may arise from the fact that it is used by one of the extra cost “management packs”.

Update 2014-08-08: Here is my later post on DBMS_COMPARISON.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s