Re: Oracle Streams and Uniqueness

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_at_evdbt.com> 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

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