Monday, 12 December 2016

Snapshot in Oracle Key Points.........

A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases.

Snapshot connected to a Single Master Site:

Snapshots can also contain a WHERE clause so that snapshot sites can contain customized data sets. Such snapshots can be helpful for regional offices or sales forces that do not require the complete corporate data set.When a snapshot is refreshed, Oracle must examine all of the changes to the master table to see if any apply to the snapshot. Therefore, if any changes where made to the master table since the last refresh, a snapshot refresh will take some time, even if the refresh does not apply any changes to the snapshot. If, however, no changes at all were made to the master table since the last refresh of a snapshot, the snapshot refresh should be very quick.

Snapshot and materialized view are almost same same but with one difference.
You can say that materialized view =snapshot + query rewrite functionality query rewrite functionality:In materialized view you can enable or disable query rewrite option. which means database server  will rewrite the query so as to give high performance. Query rewrite is based on some rewritten standards(by oracle itself).So the database server will follow these standards and rewrite the query written in the materialized view ,but this functionality is not there in 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.


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.

No comments:

Post a Comment