Oracle7 Server Distributed Systems Volume II: Replicated Data

Contents Index Home Previous Next

Creating Snapshots

You create a snapshot using the SQL command CREATE SNAPSHOT. As when creating tables, you can specify storage characteristics, extent sizes and allocation, and the tablespace to hold the snapshot, or a cluster to hold the snapshot (in which case all of the previous options do not apply). You can also specify how the snapshot is to be refreshed and the distributed query that defines the snapshot; this is unique to snapshots.

For example, the following CREATE SNAPSHOT statement defines a local snapshot to replicate the remote EMP table located in NY:

CREATE SNAPSHOT emp_sf
   PCTFREE 5 PCTUSED 60
   TABLESPACE users
   STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 50)
   REFRESH FAST
      START WITH sysdate
      NEXT sysdate + 7
   AS SELECT * FROM scott.emp@sales.ny.com;

Whenever you create a snapshot, Oracle immediately fills the base table with the rows returned by the query that defines the snapshot. Thereafter, the snapshot is refreshed as specified by the REFRESH clause; see "Refreshing Snapshots" [*].

Restrictions on Snapshots

Declarative constraints on snapshots and snapshot logs are not supported.

Snapshots of LONG columns are not supported.

Naming Snapshots

Snapshots are contained in a user's schema. A snapshot's name must be unique with respect to other objects in the schema. Although a snapshot name can be up to 30 bytes, keep snapshot names to 19 or fewer bytes. If a snapshot name contains more than 19 characters, Oracle automatically truncates the prefixed names of the underlying table and views, and appends them with a four-digit number to ensure uniqueness. This guarantees that the objects comply with the naming rules for schema objects.

Creating a Clustered Snapshot

You can create a snapshot in a cluster, just as you can a table. For example, the following statement creates a snapshot named EMP_DALLAS in the EMP_DEPT cluster:

CREATE SNAPSHOT emp_dallas
   ...
   CLUSTER emp_dept
   ... ;

The storage parameters of the cluster's data segment are used for the storage of the clustered snapshot, even if storage parameters are specified for the snapshot.

Creating Complex Snapshots versus Creating Local Views

When creating a complex snapshot, consider an alternative: creating simple snapshots and performing the complex query using a view in the snapshot database. Figure 3 - 2 illustrates the advantages and disadvantages of completing the same operation by the two different methods.

Figure 3 - 2. Two Methods for Complex Snapshots

Complex Snapshot

Method A shows a complex snapshot. The snapshot in Database II exhibits efficient query performance because the join operation has already been completed during the snapshot's refresh. However, complete refreshes must be performed in this case because it is a complex snapshot.

Simple Snapshots with a Joined View

Method B shows two simple snapshots in Database II, as well as a view that performs the join in the snapshots' database. Query performance against the view would not be as good as the query performance against the complex snapshot in Method A. However, the simple snapshots can be more efficiently refreshed using snapshot logs.

In summary, to decide which method to use:

Privileges Required to Create Snapshots

To create a snapshot, you must have the following sets of privileges:

Contents Index Home Previous Next