Doc ID: Note:236233.1 Type: BULLETIN Last Revision Date: 09-DEC-2003 Status: PUBLISHED
Overview ========
The purpose of this article is to explain Oracle’s materialized view fast refresh mechanism. It also discusses the performance, problems related to materialized view log management.
NB: The terms ´snapshot’ and ‘materialized view’ are synonymous.
Article Contents
1. Fast Refresh. 1.1 Database Objects Involved In The Fast Refresh Mechanism. 1.2 Registration. 1.3 Fast Refresh Operation.
2. Snaphot Log Management. 2.1 When Snapshot Log Entries Can Be Purged ?. 2.1.1 How Fast Refresh Is Performed And When The Snapshotlog Gets Purged. 2.1.2 Complete Refresh. 2.1.3 Purge Problems. 2.1.3.1 Materialized View Logs Growing Too Large. 2.1.3.2 How To Purge Manually Snapshot Logs. 2.2 Managing Snapshot Log Space. Performance. 2.2.1 Truncating A Snapshot Log. 2.2.2 Reorganizing Master Tables That Have Snapshot Logs.
3. Related Bugs. 4. Reference.
1. FAST REFRESH.
To perform a fast refresh, the server that manages the snapshot first identifies the changes that occurred in the master since the most recent refresh of the snapshot and then applies them to the snapshot. Fast refreshes are more efficient than complete refreshes when there are few changes to the master table(s) because the participating server and network replicate a smaller amount of data. You can perform fast refreshes of snapshots only when the master table has a snapshot log.
1.1 DATABASE OBJECTS INVOLVED IN THE FAST REFRESH MECHANISM.
It is useful do understand the structure underlying snapshots and snapshot logs.
1.1.1 A snapshot consists of:
- A table in the snapshot site, which is referred to as the snapshot base table. - A materialized view, which has the same name of the snapshot and is based on the snapshot base table - An unique index on the snapshot base table - An entry in SYS.SNAP$ defining the snapshot. - An entry in SYS.SLOG$ at master site.
1.1.2 A snapshot log on a master table consists of:
- A master table on which the snapshot is based on. - A log table to hold the information about the changed rows in the master table. This table is referred to as the snapshot log table. The snapshot log is a table named MLOG$_
An entry in SYS.MLOG$ defining the snapshot log. - The snapshot log expedites the refresh process of a simple snapshot allowing a 'FAST' refresh. is used to determine with master table rows need to be refreshed down to the snapshot site, either by looking at the rowids or primary keys (in the case of PK snapshots which is the default for Oracle8). - A snapshot that is NOT simple cannot be fast refreshed so it will NOT need a snapshot log. - One snapshot log is be used for multiple snapshots based on the same master table.
For fast-refresh on ROWID snapshots, modified rows of the master table are recorded in the snapshot log using the ROWID. The rows in the snapshot base table contain the rows of the master table. During fast-refresh, distributed queries based on the recorded rows in the snapshot log and the related rows in the master table will copy the row from the master table to the snapshot base table. The snapshot log structure is as follows for ROWID snapshots.
m_row$$ varchar2(255) ROWID of master table (18 bytes) snaptime$$ date first snapshot sets initial refresh time dmltype$$ varchar2(1) Type of DML old_new$$ varchar2(1) O , N, U change_vector$$ raw(255) Used for subquery and LOB snapshots
Primary key snapshots use the master table’s primary key instead of ROWIDs to drive fast-refresh. The m_row$$ column is removed from the snapshot base table as well as from snapshot logs.
The snapshot log structure is as follows for PRIMARY KEY snapshots.
Primary key col Same as master table snaptime$$ Date first snapshot sets initial refresh time dmltype$$ varchar2(1) Type of DML old_new$$ varchar2(1) O , N, U change_vector$$ raw(255) Used for subquery and LOB snapshots.
There are 2 new columns added to the snapshot log at the master site:
- old_new$$:
This new column is added to allow the fast-refresh mechanism to distinguish between rows inserted at the snapshot site and rows with modified primary key values. It’s used for updateable snapshot logs.
There are 3 possible values: ‘O’: This entry logs a deleted row or the old values of a modified primary key ‘N’: This entry logs a newly inserted row or the new values of a modified primary key ‘U’: This entry logs an updated row where the primary key values were modified. Primary key snapshot logs will record updates to primary key values as two log entries - deletion of the row with the old primary key values. - insertion of the row with the new primary key values.
- change_vector$$:
The change_vector$$ is added to the mlog$_t1 in order to be able to fast-refresh subquery and LOB snapshots. The column is a bit vector that indicates which columns in the master table were changed by a DML statement.
1.1.3 An AFTER ROW trigger on the master table named TLOG$_.
From Oracle8 and onward this trigger is kernelized and is no longer visible in the data dictionary. The same trigger can support both ROWID and primary key snapshots and will populate the MLOG$_
with the proper values. It also populates the SNAPTIME$$ column (indicating the lastest refresh time so far a particular row) and the DMLTYPE$$ column. The snaptime$$ column is populated based on the value of the snaptime column in snap$ table at the snapshot site and is not updated until the log is first used by a snapshot refresh.
1.2 REGISTRATION.
Oracle automatically tries to register a materialized view at its master site or master materialized view site when you create the materialized view, and unregisters when you drop it. The same applies to materialized view groups. Registration of fast refreshable materialized view logs in the master database (SYS.SLOG$) is needed to perform fast refreshes. This information is also used to maintain the materialized view log of the master table.
Refer to the following article for additional information about the snapshot registration process:
Note 258634.1. Materialized View registration at Master Site.
1.3 FAST REFRESH OPERATION.
The snapshot fast-refresh mechanism in Oracle7 is based on the ROWIDs of master tables. When the master table is updated, a trigger will be fired which inserts the ROWID of the inserted, updated or deleted rows into the snapshot log at the master site. Using the ROWID of the master table has the following disadvantages:
- ROWIDs do not persist through a reorganization of master tables, forcing all snapshots to perform a complete refresh after a table reorganization. - It is not possible to perform fast-refresh of simple snapshots with subqueries.
Oracle8 introduces Primary Key snapshots. The fast-refresh mechanism in Oracle8 uses the primary key of the master table rather than ROWIDs for fast-refresh. ROWID based snapshot logs and refresh mechanism is still available in Oracle8.
A snapshot can perform a fast refresh only if it can use the snapshot log. This is determined during the setup phase by verifying the following conditions:
- The snapshot’s identifier must be present in SYS.SLOG$ - The snapshot’s last refresh time must be later than the oldest entry in the snapshot log.
Basically the refresh operation consists of 3 phases: 1. Setup. During this phase the PL/SQL RPC dbms_snapshot.set_up is called from the snapshot site. Setup has to verify if the snapshot being refreshed is a ROWID or primary key snapshot. Then it is necessary to verify if a fast-refresh can be performed. Afterwards the snaptime$$ column is updated in the snapshot log mlog$_t1 of the altered rows to its own refresh date and time for the first snapshot that refreshes. This value does not change until the rows are eventually purged from the log.
2. Refresh Operation. 2.1 Call dbms_snapshot.verify_log and determine if a fast-refresh can take place. After dbms_snapshot.set_up is called a second check is made by calling the PL/SQL RPC dbms_snapshot.verify_log which is also called from the snapshot site to ensure that each refreshing snapshot can use the snapshot log. In order for the snapshot log to be used the timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.
2.2 Delete the rows that are no longer in the master table.
2.3 Applying modifications from the master.
3. Wrap-up. The PL/SQL RPC dbms_snapshot.wrap_up called from the snapshot site checks if the least recently updated snapshot has refreshed and therefore the snapshot log entries can be purged. If the log gets purged and an error occurs after this routine the next time this snapshot refreshes, it will need to be entirely reinstantiated. Then it is checked if registration of the snapshot is required on the master. This is only the case if the snapshot id was increased/changed at some point.
2. SNAPHOT LOG MANAGEMENT.
2.1 WHEN SNAPSHOT LOG ENTRIES CAN BE PURGED ?
Oracle automatically tracks which rows in a snapshot log have been used during the refreshes of snapshots, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple snapshots can use the same snapshot log, rows already used to refresh one snapshot may still be needed to refresh another snapshot. Oracle does not delete rows from the log until all snapshots have used them.
In the wrap-up phase of the refresh process, the master snapshot log is purged. It deletes all unnecessary rows from the snapshot log. Rows in the snapshot log are unnecessary if their refresh timestamps MLOG$_
.SNAPTIME$$ are older or equal than the oldest entry in SLOG$.SNAPTIME for this log.
MLOG$_
.SNAPTIME$$ <= MIN (SLOG$.SNAPTIME)
In another words while the oldest SNAPTIME in SLOG$, for the related snapshot log, was older than MLOG$_
. SNAPTIME$$ column these rows were not never purged:
MLOG$_
.SNAPTIME$$ > MIN (SLOG$.SNAPTIME)
2.1.1. HOW FAST REFRESH IS PERFORMED AND WHEN THE SNAPSHOTLOG GETS PURGED.
In this sample we show how a fast refresh is performed and when the snapshot log gets purged.
In this case a primary key (default value) snapshot log on EMP has been created at master site. There are 3 fast refresh snapshots based in the EMP table.
When you create a snapshot log an entry in sys.MLOG$ is added. Its YOUNGEST and OLDEST_PK (or OLDEST if it is a rowid materialized view) columns are updated with the creation timestamp.
1. create snapshot log on scott.emp (at master site).
select master, to_char(oldest_pk,'mm/dd/yyyy hh24:mi:ss') OLDEST_PK, to_char(youngest,'mm/dd/yyyy hh24:mi:ss') YOUNGEST from sys.mlog$ where master='&table_name'
2. create snapshot emp refresh fast as select * from scott.emp@master.world;
When we create a snapshot, at master site:
2.1 If a materialized view log exists the corresponding row in SYS.MLOG$ is updated. Its YOUNGEST column is updated with the creation timestamp. If there are not other materialized views of this master the OLDEST_PK (or OLDEST if it is a rowid materialized view) it also updated with the same value.
select master, to_char(oldest_pk,'mm/dd/yyyy hh24:mi:ss') OLDEST_PK, to_char(youngest,'mm/dd/yyyy hh24:mi:ss') YOUNGEST from sys.mlog$ where master='&table_name'
2.2 A row is inserted in SYS.SLOG$ only if a materialized view log exits. The SNAPTIME column is updated with the materialized view creation timestamp. The SNAPTIME column in SYS.SLOG$ is the underline column of dba_snapshot_logs.current_snapshots.
COLUMN snapshot_id HEADING 'SnapshotID' FORMAT b9999999999 COLUMN owner HEADING 'Owner' FORMAT A6 COLUMN name HEADING 'Mview Name' FORMAT A30 COLUMN snapshot_site HEADING 'Mview Site' format a30 COLUMN current_snapshots HEADING 'Last Time Refresh' format a21
select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site, to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots from dba_registered_snapshots r, dba_snapshot_logs l where r.snapshot_id = l.snapshot_id (+) and l.master='&table_name'
SnapshotID Owner Mview Name Mview Site Last Time Refresh ----------- ------ ------------------------------ ------------------------------ ------------------- 331 SCOTT EMP_SNAP SNAP_SITE 02/12/2003 11:19:13
3. After creating the remaining two snapshots. We execute the same queries:
3.1 The YOUNGEST column in SYS.MLOG$ is updated with the last creation timestamp. As there are other materialized views of this master the OLDEST_PK (or OLDEST if it is a rowid materialized view) it does not update.
3.2 A row for each materialized view is inserted in SYS.SLOG. The SNAPTIME column is updated with the materialized view creation timestamp.
SnapshotID Owner Mview Name Mview Site Last Time Refresh ----------- ------ ------------------------------ ------------------------------ ------------------- 331 SCOTT EMP_SNAP SNAP_SITE 02/12/2003 11:19:13 332 SCOTT EMP_SNAP1 SNAP_SITE1 02/12/2003 11:50:43 333 SCOTT EMP_SNAP2 SNAP_SITE2 02/12/2003 11:52:21
4. Changes are made to Master Table: EMP. The internalized trigger fires and updates the snapshot log (MLOG$_EMP) .
4.2 The trigger records, in this case primary key information, as well as update MLOG$_EMP.SNAPTIME$$ with a default value 01-JAN-00 (01/01/4000 12:00:00). In this way we indicate that no snapshots have refreshed these records yet.
5. When the first snapshot is refreshed: EXEC DBMS_SNAPSHOT.REFRESH('SCOTT.EMP_SNAP1','F');
The MLOG$_EMP.snaptime$$ column is populated based on the value of the snaptime column in snap_reftime$ table at the snapshot site. At the master site the SYS.MLOG$ table keeps track of the least recent (OLDEST or OLDEST_PK column) if necessary and most recent (YOUNGEST) refresh times for a particular master table, SLOG$ table keeps track of the snapshots for a particular master table based last refresh time SLOG$.SNAPTIME matches the corresponding column in SNAP_REFTIME$ at the snapshot site. After the first refresh we execute the above queries:
SnapshotID Owner Mview Name Mview Site Last Time Refresh ---------- ------ ------------------------------ ------------------------------ ------------------- 331 SCOTT EMP_SNAP M2V817.ES 02/12/2003 11:19:13 332 SCOTT EMP_SNAP1 M2V817.ES 02/12/2003 12:18:51* 333 SCOTT EMP_SNAP2 M2V817.ES 02/12/2003 11:52:21
The column MLOG$_EMP.SNAPTIME$$ of the related rows has been update with the refresh time: 02/12/2003 12:18:51. In this refresh process the OLDEST_PK column has not been update because it's still the oldest refresh time.
Can we purge the snapshot log now? The oldest SYS.SLOG$.SNAPTIME for EMP is compared with MLOG$_EMP.SNAPTIME$$. Since the oldest SLOG$.SNAPTIME is before 02/12/2003 12:18:51 , it does not purge the snapshot log. (There are still 2 snapshots that have not yet refreshed the rows in MLOG$_EMP)
6. When the second snapshot is refreshed. At the master site the SYS.MLOG$ table keeps track of the least recent (OLDEST or OLDEST_PK column) if necessary and most recent (YOUNGEST) refresh times for a particular master table, SLOG$ table keeps track of the snapshots for a particular master table based last refresh time SLOG$.SNAPTIME matches the corresponding column in SNAP_REFTIME$ at the snapshot site. The MLOG$_EMP.snaptime$$ column is not updated. In this refresh process the OLDEST_PK column has been updated with the oldest refresh time in SLOG$.SNAPTIME.
SnapshotID Owner Mview Name Mview Site Last Time Refresh ---------- ------ ------------------------------ ------------------------------ ------------------- 331 SCOTT EMP_SNAP M2V817.ES 02/12/2003 12:40:39* 332 SCOTT EMP_SNAP1 M2V817.ES 02/12/2003 12:18:51 333 SCOTT EMP_SNAP2 M2V817.ES 02/12/2003 11:52:21
Can we purge the snapshot log now? The oldest SYS.SLOG$.SNAPTIME for EMP is compared with MLOG$_EMP.SNAPTIME$$. Since the oldest SLOG$.SNAPTIME is before 02/12/2003 12:18:51 , it does not purge the snapshot log. (There are still 1 snapshot that has not yet refreshed the rows in MLOG$_EMP)
7. When the last snapshot is refreshed. At the master site the SYS.MLOG$ table keeps track of the least recent (OLDEST or OLDEST_PK column) if necessary and most recent (YOUNGEST) refresh times for a particular master table, SLOG$ table keeps track of the snapshots for a particular master table based last refresh time SLOG$.SNAPTIME matches the corresponding column in SNAP_REFTIME$ at the snapshot site. In this refresh process the OLDEST_PK column has been updated with the oldest refresh time in SLOG$.SNAPTIME.
SnapshotID Owner Mview Name Mview Site Last Time Refresh ---------- ------ ------------------------------ ------------------------------ ------------------- 331 SCOTT EMP_SNAP M2V817.ES 02/12/2003 12:40:39 332 SCOTT EMP_SNAP1 M2V817.ES 02/12/2003 12:18:51 333 SCOTT EMP_SNAP2 M2V817.ES 02/12/2003 12:50:27
Can we purge the snapshot log now?
The oldest SYS.SLOG$.SNAPTIME for EMP is compared with MLOG$_EMP.SNAPTIME$$. Since the oldest SYS.SLOG$.SNAPTIME is now the same as that in MLOG$_EMP, Oracle knows that all snapshots have refreshed those records in MLOG$_EMP. The rows with a MLOG$_EMP.SNAPTIME$$ equal to or older than the oldest SYS.SLOG$.SNAPTIME for EMP are purged.
2.1.2 COMPLETE REFRESH.
In an environment with multiples snapshots for a master table. The complete refresh does the same mlog management than fast refresh.
2.1.3 PURGE PROBLEMS.
2.1.3.1. MATERIALIZED VIEW LOGS GROWING TOO LARGE.
As a result of how Oracle purges rows from a snapshot log, unwanted situations can occur that cause a snapshot log to grow indefinitely when multiple snapshots are based on the same master table. For example, such situations can occur when more than one snapshot is based on a master table and one of the following conditions is true:
- One snapshot is not configured for automatic refreshes and has not been manually refreshed for a long time.
- One snapshot has an infrequent refresh interval, such as every year (365 days).
- A network failure has prevented an automatic refresh of one or more of the snapshots based on the master table.
- A network or site failure has prevented a master from becoming aware that a snapshot has been dropped. There is still an orphan entry for this snapshot in SYS.SLOG$ at master site and then its SNAPTIME in SLOG$ will not longer be updated. The oldest value for SNAPTIME will always be older than the MLOG$_EMP.SNAPTIME$$. This means that the entries in MLOG$_EMP will never be purged during a refresh.
Refer to the following articles for additional information about to find orphan snapshots.
Note 258634.1. Materialized View registration at Master Site. Note 236292.1. Scripts to Report Information about Materialized View Logs at the Master Site.
In this situation you may need to purge part of the materialized view log or unregister the unused materialized view. The clean up of a snapshot at master site includes. For each 'orphan' snapshot.
a. You should purge the associated snapshot log of the entries that were marked for the target snapshot
It will fail with error ORA-23424. In this case you'd need to use the procedure based on the identification of the target snapshot.
BEGIN DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (snapshot_id); END; / You can find the snapshot id in dba_snapshot_logs view or dba_base_table_mviews view in Oracle9 and onwards.
2.1.3.2 HOW TO PURGE MANUALLY SNAPSHOT LOGS.
The Snapshot Logs can be purged using the following Oracle Replication procedures within the DBMS_SNAPSHOT package through one of the following interfaces:
Note: DBMS_SNAPSHOT is a synonym for DBMS_MVIEW.
2.1.3.2.1 To manually purge rows from a snapshot log, execute the PURGE_LOG stored procedure of the DBMS_SNAPSHOT package at the database that contains the log. For example, to purge entries from the snapshot log of the CUSTOMERS table that are necessary only for the least recently refreshed snapshot, execute the following procedure:
BEGIN DBMS_SNAPSHOT.PURGE_LOG ( master => 'sales.customers', num => 1, flag => 'DELETE'); END; /
2.1.3.2.2. The procedure DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG is called on the master site to delete the rows in snapshot refresh related data dictionary tables maintained at the master site for the specified snapshot identified by its snapshot_id or the combination of the snapowner, snapname, and the snapsite. If the snapshot specified is the oldest snapshot to have refreshed from any of the master tables, then the snapshot log is also purged. This procedure does not unregister the snapshot. In case there is an error while purging one of the snapshot logs, the successful pur snapshot logs are not rolled back. This is to minimize the size of the snapshot logs. In case of an error, this procedure can be invoked again until all the snapshot logs are purged Then this procedure will remove entries from the snapshot log (MLOG$_
) as well as remove the entry of that snapshot from the sys.slog$ table.
This procedure has two interfaces:
PROCEDURE purge_snapshot_from_log( snapowner IN VARCHAR2, snapname IN VARCHAR2, snapsite IN VARCHAR2);
PROCEDURE purge_snapshot_from_log( snapshot_id IN BINARY_INTEGER);
If the snapshot has been purged due a mistake and the related entry in SYS.SLOG$ has been deleted. Next fast refresh will fail, you must execute a complete refresh that will once again register the snapshot by inserting an entry into the sys.slog$ table.
2.1.3.2.3 To unregister the snapshot from regsnap$ it is necessary to execute.
DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (snapowner IN VARCHAR2, snapname IN VARCHAR2, snapsite IN VARCHAR2);
2.2 MANAGING SNAPSHOT LOG SPACE. PERFORMANCE.
The space used by the snapshot is not a function of the refresh workload in the sense that the snapshot log does not grow as the number of snapshots based on the master table increase. However, if some of the snapshots based on the same master table are not refreshed at the same time as the others, the rows required to refresh that snapshot will still be kept in the snapshot log, causing it to grow. In our test environment all the snapshots are refreshed concurrently which eliminated that possibility. The size of the snapshot log for ROWID snapshots is only a function of number of rows in the log, as the log does not depend on the actual row size in the table. Primary Key snapshot logs, on the contrary, include the primary key of the table in the snapshot log and this makes the snapshot log space usage a function of primary key column length combined with the number of rows in the snapshot log. When designing snapshots there are a few things to keep in mind regardless of the snapshot type:
- Keep the snapshot log as small as possible. The size of the snapshot log directly has an effect on the fast refresh performance. Since there is no index on the snapshot log, each refresh operation has to do a full table scan on this table. If there are some snapshots that are not fast-refreshed for a long time, this may cause the log to grow out of bounds. In this case it is advisable to purge the log at the expense of a complete refresh of a few snapshots. The snapshot registration feature in Oracle8 may come very handy in identifying these snapshots.
- Tune disk IO for the master tables and snapshot logs. Striping the snapshot log and the master table datafiles is as important as striping other data and index datafiles. When multiple snapshots start refreshing from the same snapshot concurrently the heavy disk read requests of full scans can easily saturate the disk IO subsystem.
2.2.1 TRUNCATING A SNAPSHOT LOG.
If a snapshot log grows and allocates many extents, purging the log of rows does not reduce the amount of space allocated for the log. To reduce the space allocated for a snapshot log:
1. Acquire an exclusive lock on the master table to prevent updates from occurring during the following process. For example, issue a statement similar to the following:
LOCK TABLE scott.emp IN EXCLUSIVE MODE;
2. Using a second database session, copy the rows in the snapshot log (in other words, the MLOG$ base table) to a temporary table. For example, issue a statement similar to the following:
CREATE TABLE scott.templog AS SELECT * FROM scott.mlog$_emp;
3. Using the second session, truncate the log using the SQL statement TRUNCATE. For example, issue a statement similar to the following:
TRUNCATE scott.mlog$_emp;
4. Using the second session, reinsert the old rows so that you do not have to perform a complete refresh of the dependent snapshots. For example, issue a statement similar to the following:
INSERT INTO scott.mlog$_emp SELECT * FROM scott.templog; DROP TABLE scott.templog;
5.Using the first session, release the exclusive lock on the master table by performing a rollback:
ROLLBACK;
Note: Any changes made to the master table between the time you copy the rows to a new location and when you truncate the log do not appear until after you perform a complete refresh. Then it’s better to truncate the snapshot log when it is empty. Only the owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can truncate a snapshot log.
2.2.2 REORGANIZING MASTER TABLES THAT HAVE SNAPSHOT LOGS.
To improve performance and optimize disk use, you can periodically reorganize tables. This section discusses how to reorganize a master table and preserve the fast refresh capability of associated snapshots.
- Reorganization Notification.
When you reorganize a table, any ROWID information of the snapshot log must be invalidated. Oracle detects a table reorganization automatically only if the table is truncated as part of the reorganization. See "Method 2 for Reorganizing Table T".
If the table is not truncated, Oracle must be notified of the table reorganization. To support table reorganizations, two procedures, DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION, notify Oracle that the specified table has been reorganized. The procedures perform clean-up operations, verify the integrity of the logs and triggers that the fast refresh mechanism needs, and invalidate the ROWID information in the table's snapshot log. The inputs are the owner and name of the master table to be reorganized. There is no output.
- Truncating Master Tables.
When a table is truncated, its snapshot log is also truncated. However, for primary key snapshots, you can preserve the snapshot log, allowing fast refreshes to continue. Although the information stored in a snapshot log is preserved, the snapshot log becomes invalid with respect to ROWIDs when the master table is truncated. The ROWID information in the snapshot log will seem to be newly created and cannot be used by ROWID snapshots for fast refresh.
If you specify the PRESERVE SNAPSHOT LOG option or no option, the information in the master table's snapshot log is preserved, but current ROWID snapshots can use the log for a fast refresh only after a complete refresh has been performed. This is the default behaviour.
If the PURGE SNAPSHOT LOG option is specified, the snapshot log is purged along with the master table.
Examples:
Either of the following two statements preserves snapshot log information when the master table named ORDERS is truncated:
The following statement truncates the snapshot log along with the master table:
TRUNCATE TABLE orders PURGE SNAPSHOT LOG;
3. RELATED BUGS.
Bug 2259259. MLOG$ entries may not be purged after refresh for a 9i master site with an 8i snapshot site. This can result in snapshot refreshes taking longer and longer each time. Fixed-Releases: 9.0.1.4, 9.2.0.2, 10. Bug 1282043. FAST REFRESH ON MATERIALIZED VIEWS TOO SLOW COMPARED TO COMPLETE REFRESH. Fixed-Releases. 9.0.1.x Bug 2685250. DBMS_REPCAT_RGT.INSTANTIATE_ONLINE ADD ENTRIES TO SLOG$. Fixed-Releases. 10.0 Bug 945661. SNAPSHOT STILL FAST REFRESHED AFTER DBMS_SNAPSHOT.PURGE_LOG. Fixed-Releases. 8.1.6
4. REFERENCES.
See also:
Oracle9i Replication. Oracle9i Replication Management API Reference.
