Tim, Well I don't immediately see why a statement at the source such as delete from t1 where c1 = '42'; absolutely requires each row in t1 to be unique in order for the delete to be successfully replicated to a remote target (this is the nature of our problem transactions). I would have hoped that in a solution that trumpets itself with the description "sql apply" this would turn into delete from t1 where c1 = 42; at the remote side, rather than 300,000 executions of delete from t1 where some_unique_key = :1 . :( I guess all this really shows is that I don't know enough about the theory of database replication. As for blaming Oracle for assuming that database apps are designed in accordance with relational principles, I think I do somewhat. I'm pretty sure that by about 1999/2001 at the latest that assumption was widely known to be untrue. Niall On Mon, Nov 10, 2008 at 4:26 PM, Tim Gorman <tim@xxxxxxxxx> wrote: > Niall, > > It is not a restriction only of Oracle Streams, but of absolutely any > replication solution, anywhere, at any time. How can any UPDATE or DELETE > be propagated with a way of uniquely identifying rows, regardless of > technology, whether using Oracle or Sybase or DB2 or MySQL, or any mixture > of these? > > A few years ago, I was on a project where we had Quest and Golden Gate each > claiming that their respective products, unlike Oracle Advanced Replication > and Oracle Streams, imposed no such restriction. Which is true -- they > didn't actually impose the restriction. The Oracle products simply imposed > the restriction before it caused logical data corruption. > > Supplemental redo logging enables replication by recording the "logical > ROWID" data values (i.e. PK/UK/substitute-key) in the generated redo, along > with the usual "physical ROWID" values, for each change. For example, prior > to the advent of Streams and supplemental redo logging, the Quest SharePlex > product had to perform a "lookup" (after the redo was replicated) by > "physical ROWID" on the source database in order to obtain the "logical > ROWID" data values. The same is almost certainly true of other log-based > replication products on Oracle prior to Oracle9i as well. > > Still, none of this implies that declarative PK or UK constraints have to > be created in the source database. The combination of columns that > represent uniqueness can alternatively be recorded into the metadata of > Oracle Streams or Oracle Advanced Replication; declarative PK or UK > constraints are simply a convenience. Of course, if neither of these are > found, then Streams/AdvRep is forced to assume that all columns in > combination represent uniqueness, which is not always true either. In which > case, should the blame be placed on Streams/AdvRep for assuming that the > application was designed according to well-established relational database > theory? > > Hope this helps... > > -Tim > > > > > > Niall Litchfield 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 > > > -- Niall Litchfield Oracle DBA http://www.orawin.info