Re: Oracle Streams and Uniqueness

Assuming the application doesn't "care" about uniqueness, you can probably assess the impact on various DML statements. For INSERTS, I'm guessing you're somehow making sure you don't get duplicates, or you don't care about them. In those cases, Streams should faithfully replicate the duplicates. For UPDATES and DELETES it becomes problematic, as Streams tries to generate row-by-row change statements, so it needs to uniquely identify the rows.  This, of course, isn't how you probably specified the original command -- you want to work on a SET, not on the individual ROWS, so you don't care which rows get handled as long as the SET you've identified gets handled correctly. I'm not sure if Streams cares about how many rows get DELETED / UPDATED -- although I think it cares if the rows exist or not to begin with.  Curious -- are you getting errors in the apply logs or are you still at the conceptual phase in working with Support?

Back in the days of Advanced Replication (pre-Streams), we supported the idea of Procedure Replication -- a way for you to replicate a procedure call instead of relying on the row-by-row triggers. In the procedure call, you'd signal the triggers NOT replicate the row-by-row changes and the Procedure Call would be sent -- that simulated the SET command. I'm not sure if Streams has this concept.

All of this gets really hairy if you're allowing multiple sites to do DML, since it screams data corruption and conflict.

On Mon, Nov 10, 2008 at 9:21 AM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> 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
>
>

-- 
Dominic Delmolino

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