Lesson 4	Using primary key snapshots in replication
Objective	Create primary key snapshots in replication.

Use Primary Key Snapshots Replication in Oracle

When you create a snapshot, Oracle creates another table to keep track of changes to the master table. This new table name is the same as the master table name, except that it is prefixed by MLOG$. For example, a customer master log entitled CUSTOMER would have a customer snapshot master log called MLOG$CUSTOMER. Whenever a change is made to the master table, Oracle fires an internal trigger to post the change to the MLOG$_CUSTOMER table. When the refresh interval is met, Oracle goes to the master log to gather the IDs of all rows that have changed and propagates the IDs to the remote databases. In Oracle7, all snapshots were ROWID snapshots . That is, the logs contained the ROWIDs of the changed rows.
These ROWIDs were used to find the changed rows in the master table when it was time to propagate the rows. In Oracle, ROWID snapshots have been replaced by primary key snapshots.
With primary key snapshots, the snapshot log table holds the primary key for each row that has changed.

ROWID snapshot: A copy of a table. Changed rows are refreshed by retrieving the row using its ROWID.
Primary key snapshot: A copy of a table or view. Changed rows are refreshed by retrieving the row using its primary key value.

The MLOG$CUSTOMER snapshot log is created at snapshot definition time. The snapshot log contains the primary key of any row that was changed since the last time the snapshot was refreshed.
When the refresh interval is reached, Oracle will perform the following three steps:

Primary Key Snapshots Mechanism

How PrimaryKey Snapshots Work
In Oracle and Oracle, all snapshots are primary key snapshots by default. If you want to use the Oracle ROWID snapshot, you must add REFRESH WITH ROWID to the snapshot definition.

Advanced Primary Key Snapshots

Primary key snapshots may be quite complex, and they may even contain sub queries. This is useful in cases where a reference to another table is required to constrain the snapshot. The following code is an example of a snapshot with a WHERE clause containing a correlated sub query:

CREATE SNAPSHOT 
   BAD_CUSTOMER
AS
 SELECT * 
FROM CUSTOMER@DENVER MASTER
 WHERE EXISTS
   (SELECT 1 FROM BAD_CREDIT@DENVER BAD
    WHERE MASTER.CUST_ID = BAD.CUST_ID);

In the example above, we have created a primary key snapshot called BAD_CUSTOMER. The snapshot will keep track of customer rows where a corresponding row exists in the BAD_CREDIT table.
Now that we understand the basics of Oracle primary key snapshots, let us take a look at Oracle's new updateable snapshot feature.

Distributed Databases