Oracle Distributed Systems

Simple Versus Complex Snapshots

Simple snapshots are the only type that can use the FAST REFRESH method. A snapshot is considered simple if the defining query meets the following criteria:

• It does not contain any DISTINCT or aggregation functions.

• It does not contain a GROUP BY or CONNECT BY clause.

• It does not perform set operations (UNION, UNION ALL, INTERSECT, etc.).

• It does not perform joins other than those used for subquery subsetting.

Essentially, a simple snapshot is one that selects from a single table and that may or may not use a WHERE clause.

Note

Oracle8 extends the universe of simple snapshots with a feature known as subquery subsetting, described in the later section entitled “Subquery Subsetting.”

Not surprisingly, any snapshot that is not a simple snapshot is a complex snapshot. Complex snapshots can only use COMPLETE refreshes, which are not always practical. For tables of more than about 100,000 rows, COMPLETE refreshes can be quite unwieldy.

You can often avoid this situation by creating simple snapshots of individual tables at the master site and performing the offending query against the local snapshots. For example, avoid creating a complex snapshot such as the following:

CREATE SNAPSHOT sales_by_region
REFRESH COMPLETE
START WITH sysdate
NEXT sysdate + 1
AS
SELECT  r.region_name,
        r.sales_rep,
        p.product_id,
        count(*) num_sold,
        sum(sales_price)
FROM    [e-mail] p,
        [e-mail] r
WHERE   p.region_id = r.region_id

which must reinstantiate the ...