Getting Started with Oracle database--How to quickly refresh materialized views based on materialized view logs

The fast refresh mechanism for Oracle materialized views is done through materialized view logs. How Oracle can support a fast refresh of multiple materialized views through a materialized view log, this article simply describes how the refresh works.

First, consider the structure of the materialized view:
Sql> CREATE TABLE T (ID number, name VARCHAR2 (), num number);
The table is created.

Sql> create materialized view Log on T with ROWID, sequence (ID, name) including new values;
The manifested view log has been created.

Sql> desc mlog$_t

The ID and name are the columns in the base table that are specified when the materialized view log is established, and they record the ID and the value of name for each DML operation.
M_row$$ saves the ROWID information for the base table, and the information in the m_row$$ can be used to locate the record in which the DML operation occurred.
Sequence$$ records the number of sequences based on the order in which the DML operations occur, and when refreshed, the order in sequence$$ can be consistent with the order of execution in the base table.
The snaptime$$ column records when the refresh operation was made.
The dmltype$$ record values I, U, and D indicate whether the operation is an INSERT, update, or delete.
old_new$$ Indicates whether the information saved in the materialized view log is the value before the DML operation (the old value) or the value after the DML operation (the new value). In addition to the two types O and N, for update operations, you may also be represented as U.
change_vector$$ record DML operations occur on one or more of these fields.

For a detailed description of the materialized view log structure, you can refer to the documentation: materialized view log structure: http://blog.itpub.net/post/468/20498

As described above, it can be found that when the materialized view is refreshed, it is only necessary to navigate to the base table's records by m_row$$ based on the order given by the sequence$$ column, and if the update operation, navigate to the field by change_vector$$, DML operations are then repeated based on the data in the base table.

If the materialized view log is only for a materialized view, then the refresh process is so simple that all you have to do is erase the materialized view log after the refresh.

However, the materialized view log of Oracle can support the rapid refresh of multiple materialized views at the same time, that is, materialized views must also determine which materialized view log records are required for the current materialized view refresh and which are not required. Also, materialized views must determine which records in the materialized view log need to be purged and which do not need to be purged after the materialized view is refreshed.

Reviewing the structure of the materialized view log and discovering that there is only one shaptime$$ column left, how does Oracle support multiple materialized views with this column alone? Here is a small example, illustrated by an example.

Using the tables and materialized view logs established above, the following is a three fast-refreshed materialized view of this table.

Sql> Create materialized view mv_t_id Refresh fast as Select ID, COUNT (*) from the T Group by ID;
The manifested view has been created.

Sql> Create materialized view mv_t_name refresh fast as select Name, COUNT (*) from the T group by name;
The manifested view has been created.

Sql> Create materialized view mv_t_id_name Refresh fast as Select ID, Name, COUNT (*) from the T Group by ID, name;
The manifested view has been created.

Sql> INSERT INTO t values (1, ' a ', 2);
1 rows have been created.

Sql> INSERT INTO t values (1, ' B ', 3);
1 rows have been created.

Sql> INSERT INTO t values (2, ' a ', 5);
1 rows have been created.

Sql> INSERT into t values (3, ' B ', 7);
1 rows have been created.

Sql> Update t set name = ' C ' where id = 3;
1 rows have been updated.

Sql> Delete t where id = 2;
1 rows have been deleted.

Sql> commit;
Submit complete.

Sql> Select ID, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

When a DML operation occurs, the snaptime$$ column hold value in the materialized view log is 4000-01-01 00:00:00. This value indicates that the record has not been refreshed by any materialized views. The first materialized view that refreshes these records updates the value of snaptime$$ to the current refresh time of the materialized view.

sql> exec Dbms_mview.refresh (' mv_t_id ');
The PL/SQL process has completed successfully.

Sql> Select ID, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

Oracle will know that three materialized views are established on table T based on the information in the data dictionary, so materialized view records are not deleted after mv_t_id is refreshed.

Oracle's data dictionary also holds the last refresh time and current refresh State of each materialized view.

Sql> select Name, Last_refresh from User_mview_refresh_times;

Sql> Select Mview_name, Last_refresh_date, staleness from User_mviews;

These views record when each materialized view last performed a refresh operation, and whether the data in each materialized view is synchronized with the base table. Because the mv_t_id has just been refreshed, the state is fresh, and the other two because the base table has DML operations after the refresh (build), the status is Needs_compile. If a DML operation is performed on the base table at this point, the state of the mv_t_id becomes needs_compile.

Sql> INSERT INTO t values (4, ' d ', 10);
1 rows have been created.

Sql> commit;
Submit complete.

Sql> Select ID, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

Sql> Select Mview_name, Last_refresh_date, staleness from User_mviews;

The following refreshes materialized view Mv_t_id_name, the refresh operation is based on the fact that only the snaptime$$ column is larger than the current materialized view Last_refresh_date records, because the materialized view log all records snaptime$$ value than materialized view MV _t_id_name the last time the refresh was large, so all records are refreshed. For the value of the snaptime$$ column is the record for the 4000-01-01 00:00:00, the materialized view updates the value of the snaptime$$ column to the current refresh time, preserving the original value for those snaptime$$ columns that have been updated.

sql> exec Dbms_mview.refresh (' Mv_t_id_name ')
The PL/SQL process has completed successfully.

Sql> Select ID, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

Sql> Select Mview_name, Last_refresh_date, staleness from User_mviews;

If the materialized view mv_t_id is refreshed again at this point, only the id=4 of the snaptime$$ of this record is greater than the point in time mv_t_id last refreshed, so only this record is refreshed and the snaptime$$ value is not changed.

sql> exec Dbms_mview.refresh (' mv_t_id ')
The PL/SQL process has completed successfully.

Sql> Select ID, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

Sql> Select Mview_name, Last_refresh_date, staleness from User_mviews;

So far, the materialized view log has not been cleared, in fact, each time the materialized view log will attempt to delete the unused materialized view log records. The delete condition for materialized view logging is to delete those snaptime$$ columns that are less than or equal to the last refresh time of all materialized views of the base table. In the example above, since Mv_t_name has not been refreshed, its last_refresh_date is smaller than the values of all records in the materialized view log, so the materialized view logging cleanup has not occurred.

Sql> INSERT INTO t values (5, ' E ', 2);
1 rows have been created.

Sql> commit;
Submit complete.

sql> exec Dbms_mview.refresh (' Mv_t_name ')
The PL/SQL process has completed successfully.

Sql> Select ID, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

Sql> Select Mview_name, Last_refresh_date, staleness from User_mviews;

Materialized view Mv_t_name refreshes each record in the materialized view, updates the snaptime$$ time of the id=5 record, and clears all other materialized view log records.

sql> drop materialized view Log on t;
The manifested view log has been deleted.

Sql> drop materialized view mv_t_id;
The manifested view has been deleted.

Sql> drop materialized view mv_t_name;
The manifested view has been deleted.

Sql> drop materialized view mv_t_id_name;
The manifested view has been deleted.

sql> drop table t;
The table has been deleted.

Sql>

Finally, briefly summarize:
Materialized view refreshes all records with snaptime$$ greater than the last refresh time of the materialized view and updates all records that are 4000-01-01 00:00:00 to the current refresh time. For other records that are larger than the last refresh time, only the refresh does not change. Thus, when the refresh finishes, the data dictionary records the current materialized view's last refresh time at the current moment, which guarantees that all current records in the materialized view log are less than or equal to the refresh time. Therefore, each materialized view as long as the refresh is greater than the last refresh time of the record, and guarantee that after each refresh, all records time is less than the last refresh time, then no matter how many materialized views, can not affect each other using the same materialized view log for a quick refresh. When the materialized view is refreshed, the records that snaptime$$ columns are less than the last refresh time of all materialized views are purged, and the records have been refreshed by all materialized views, and it is meaningless to save them in the materialized view log.

Getting Started with Oracle database--How to quickly refresh materialized views based on materialized view logs