Fwd: Oracle Streams and Uniqueness

 From Substitute Key Columns in the Streams Replication Administrators Guide (10gR2)

"In the absence of substitute key columns, primary key constraints, and unique key constraints, an apply process uses all of the columns in the table as the key columns, excluding LOB, LONG, and LONG RAW columns. In this case, you must create an unconditional supplemental log group containing these columns at the source database. Using substitute key columns is preferable when there is no primary key constraint for a table because fewer columns are needed in the row LCR."

It seems only fair - the apply process has to know to which row to apply a change described in an LCR... In effect, it has to make an update statement

update table set d1=, d2=, d3=, d4= ...
where k1= and k2= and k3=

where d1-n are the "data" columns (all columns if the key is updateable) and k1-n are the "key" columns. Same for delete - inserts aren't a problem of course.

Obviously the ROWID is no use, as there is no feasible mapping from rowid in the source database to rowid in the target.

However, although I wrote my own cheap and cheerful replication tools in 1993 for a client, I have no IRL experience of Oracle Streams... perhaps it can do magic?

Regards Nigel

2008/11/10 Niall Litchfield <niall.litchfield_at_gmail.com>

 All,
>
> I am being told by a support analyst at Oracle re Streams that
>
> Oracle must be able to identify uniquely and match corresponding rows at
> different
> databases.
> So each table in a Streams environment should have a primary key, unique
> key constraints or a substitute key.
>
> This in addition to supplemental logging. Is this correct? If so, surely
> this implies that streams is only useful in environments where tables are
> guaranteed (by Oracle or the application(s) running against it) to be
> unique. This seems a somewhat significant real-world disadvantage to me,
> based on the applications that I see. I guess I sort of hoped that Oracle
> might have used the ROWID to generated the LCR (in a way that I fully admit
> I have spend no time at all thinking about).
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
>

--
http://www.freelists.org/webpage/oracle-l