The Infamous Update on a Primary Key

Today I was put in front of a new challenging situation. We have a reporting application which is fed from another system using materialized views. In short, on the remote source system, for several “interesting” tables materialized view logs were defined with the “WITH PRIMARY KEY” option. These logs are used to support a fast refresh mechanism for the corresponding materialized views created on the reporting system. Each materialized view was created on a pre-built table. Of course, there’s a refresh job which takes place on regular intervals in order to keep in sync both systems. As soon as a new record arrives or an existing one is updated this is marked accordingly into a flag column within the pre-built table through a trigger. Likewise, the operation type (INSERT or UPDATE) is written as well into the pre-built table. Now, the big problem is the fact that some modules from the source system are updating (yes, very ugly!!!) values from columns which are part of the primary key. From the materialized view log perspective this is converted into a delete and an insert therefore as soon as the DBMS_MVIEW.REFRESH procedure is called on the reporting system the BEFORE/AFTER DELETE and BEFORE/AFTER INSERT triggers are executed instead of a BEFORE/AFTER UPDATE trigger. Now, one of the MV interface requirement was to convert this sequence of one DELETE and one INSERT so that on the reporting system that initial UPDATE on the primary key to look like a regular update. Even at the very beginning this might appear as a trivial task in fact is quite cumbersome. The solution we found so far relies on using ROWIDs for our pre-built tables.

Okey, because the so called “too much sound but little sense” is not going to be the subject of this post, let’s delve into a concrete test scenario.

First of all, create the source table:

create table muci (col1 integer, col2 integer, col3 integer);
alter table muci add primary key (col1, col2);

insert into muci values (1, 1, 1);
insert into muci values (2, 2, 2);
insert into muci values (3, 3, 3);

commit;
create materialized view log on muci with primary key;

Now, let’s create the pre-built table and the materialized view.

create table muci_mv as 
select t.rowid rid, t.*, '1' processed, 'INSERT' operation from muci t;

create materialized view muci_mv 
on prebuilt table 
refresh fast on demand as 
select t.rowid rid, t.* from muci t;

It is important to notice that we’ve created the pre-built table with additional columns: the “processed” and the “operation” fields. These fields are going to be filled in using a trigger on the MUCI_MV table. Also, pay attention to the “rid” field which is supposed to store ROWID addresses for all records refreshed from the MUCI table (not ROWIDs of MUCI_MV records!). I’ll show you right in a minute what the reason of this column is.

Let’s go on creating a very simple package:

create or replace package MUCI_TOOLKIT 
is

  g_old_rid rowid;

end MUCI_TOOLKIT;

This package has a global variable which is intended to store the old value of the “rid” column of a record which is about to be deleted.

Our test scenario is almost finished! The final step is to create a trigger onto the MUCI_MV table which actually takes care of updating the additional flags:

create or replace trigger trg_biudr_muci_mv
  before insert or update or delete on muci_mv  
  for each row
declare
begin
  if inserting then
    dbms_output.put_line('inserting...');
    :new.processed := '0';
    if muci_toolkit.g_old_rid is not null and :new.rid = muci_toolkit.g_old_rid then
      :new.operation := 'UPDATE';
      muci_toolkit.g_old_rid := null; -- cleanup
    else
      :new.operation := 'INSERT';
    end if;
  end if;
  if updating then
    dbms_output.put_line('updating...');
    :new.processed := '0';
    :new.operation := 'UPDATE';
  end if;
  if deleting then
    dbms_output.put_line('deleting...');
    muci_toolkit.g_old_rid := :old.rid;
  end if;
end trg_biudr_muci_mv;

Now, it’s time to effectively test!

external_interface@pdmdb> set serveroutput on
external_interface@pdmdb> select * from muci;

 COL1  COL2  COL3
----- ----- -----
    1     1     1
    2     2     2
    3     3     3

external_interface@pdmdb> select * from muci_mv;

RID                 COL1  COL2  COL3 PROCESSED OPERATION
------------------ ----- ----- ----- --------- ---------
AACdH+AAFAADheGAAA     1     1     1 1         INSERT
AACdH+AAFAADheGAAB     2     2     2 1         INSERT
AACdH+AAFAADheGAAC     3     3     3 1         INSERT

external_interface@pdmdb> update muci set col1='123' where col1=1 and col2=1;

1 row updated

external_interface@pdmdb> commit;

Commit complete

external_interface@pdmdb> exec dbms_mview.refresh('MUCI_MV', 'f');

deleting...
inserting...

PL/SQL procedure successfully completed

external_interface@pdmdb> select * from muci_mv;

RID                 COL1  COL2  COL3 PROCESSED OPERATION
------------------ ----- ----- ----- --------- ---------
AACdH+AAFAADheGAAB     2     2     2 1         INSERT
AACdH+AAFAADheGAAC     3     3     3 1         INSERT
AACdH+AAFAADheGAAA   123     1     1 0         UPDATE

As you can see it works as expected… smooth! The whole trick consists in checking if the DELETE and INSERT sequence refers to the same source ROWID. If this is the case then we know that an update on the primary key was involved.

However, there are some open points regarding this approach:
1. additional storage is required for storing the added RID column;
2. the DBMS_MVIEW.REFRESH mechanism must follow the sequence order of DML commands performed on the source system which, as far as the oracle documentation states, is not always guaranteed in all Oracle versions.
3. in some MetaLink notes (see Note:67424.1) it is stated that triggers are not supported on read only materialized views.. Yet, you can find a HOWTO on the same MetaLink (176213.1) which advise you to create a trigger on a prebuilt table in order to fill in an additional column (as shown above) with the current SYSDATE value.
4. because we rely on ROWID values of course we’ll end up in big problems if on the source site some source tables are moved to another tablespace or they are re-imported.