Document ID:        35193.1
Subject:            Why Unique Indexes Cannot be Used on Snapshot Base Tables
Author:             toscroft
Last Revision Date: 18 March     1996


This article explains why unique indexes cannot be used on snapshot base tables.

PRIMARY KEYS AND SNAPSHOTS
--------------------------

There is a restriction (documented in Oracle7 Server Distributed Systems, 
Vol II: Replicated Data v7.2 pp 2-4, 4-8) that unique or primary key 
constraints cannot be created on the snapshot base table. 

The reason for this is that the refresh mechanism, for performance reasons, may 
temporarily leave the data in an inconsistent state with the possibility of 
duplicates. During the refresh, all changes applied to the master since the 
last refresh are applied to the snapshot in one big transaction, not 
necessarily in the order in which they were originally applied.

This can be illustrated with a table containing one column with two rows, one 
of which holds the value 1 and the other 2; a primary key is created on this 
column. On the master the value 2 is updated to be 3, then the value 1 is 
updated to be 2. When the snapshot is refreshed, it is possible that the value 
1 will be updated to 2 before the 2 is updated to 3. This would cause a 
violation of a uniqueness constraint on the snapshot base table and the refresh 
would fail.    

With read-only snapshots this should not be an issue as uniqueness can be
enforced at the master site, guaranteeing that the snapshot, once the refresh 
has completed, will also contain unique values for the constrained columns.

For updatable snapshots, uniqueness constraints, at the moment, need to be
enforced using triggers. 

It will be possible, in a future release (not yet known), to enforce 
declarative constraints on snapshots. Non-unique indexes can be created safely 
to improve performance.