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
T1and with a column
COL1and a unique constraint on
create table T1 (COL1 number,To identify the rows in the database, you can query their
constraint T1_UK unique(COL1));
insert into T1(COL1) values(1);
insert into T1(COL1) values(2);
select ROWID, COL1With 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);
Note:If you don't trust me, check the
That's not the case with all the RDBMS; but if you work with Oracle that's not something you
ROWIDafter the update:
select ROWID, COL1Just 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" pSo? 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.
where (:3='N') and (:4="COL1")
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
DELETEas 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.