Oracle Streams Can Do Things, Mortals Can't!

In my last post, "Do Your Tables Need Keys Be Replicated With Streams?", I've introduced the "Manual Streams Simulator". The goal was to show how Oracle Streams works by using the simplest model we can. What I'll be showing now is that, like every tool or model, the "Manual Streams Simulator" has its limits. Actually I'll be pointing one of the things Streams can do, that you and your programs can NOT...

So, this post is about one of the many challenges Oracle Streams faces... because of its design. A very simple example will help you understand what I mean. Let's consider a table T1 and with a column COL1 and a unique constraint on COL1:

create table T1 (COL1 number,
                constraint T1_UK unique(COL1));

insert into T1(COL1) values(1);
insert into T1(COL1) values(2);
commit;

To identify the rows in the database, you can query their ROWID:

select ROWID, COL1
 from T1;

ROWID              COL1
------------------ ----
AAATF5AABAAAVxpAAA    1
AAATF5AABAAAVxpAAB    2

With Oracle and with a single update, you turn the value 1 into 2 and turn the value 2 into 1; here is how:

update T1 set COL1=decode(COL1,1,2,1)
where COL1 in (1,2);
commit;

Note:
That's not the case with all the RDBMS; but if you work with Oracle that's not something you

If you don't trust me, check the ROWID after the update:

select ROWID, COL1
 from T1;

ROWID              COL1
------------------ ----
AAATF5AABAAAVxpAAA    2
AAATF5AABAAAVxpAAB    1

Just think about it: the Capture process or the synchronous capture would turn that update into 2 LCRs that would be applied by 2 differents statements by the server apply process. If you trace the Apply server process, you'll see each statement actually look like that:

update /*+ streams restrict_all_ref_cons  */ "DESTINATION"."T1" p
   set "COL1"=decode(:1,'N',"COL1",:2)
 where (:3='N') and (:4="COL1")

So? Do you see what I mean? If you want to execute 2 separate update statements to change 1 into 2 and 2 into 1, you will need to defer the unique constraint, right? A Streams apply executes the 2 updates without any defer! The reason I'm so sure, is because it will work even if the constraint is not deferrable on the destination.

How does it manage to do it? Obviously that's an internal trick! What I know for sure is that the SCN of the capture is involved with that and I strongly suspect the /*+ streams */ hint is part of the deal. But unless you get your hand on the code, I doubt you'll manage to guess more.

The good news is that you can leverage that internal trick for your own; in my next post, I'll show you how you could use Streams to run an INSERT and a DELETE as if they where executed at the exact same SCN. It will be posted with my other posts in my section of The Pythian Group Blog. Just be patient, I'll keep you updated.