How Does Oracle quickly refresh the materialized view based on the materialized view log

The quick refresh mechanism of Oracle materialized view is completed by materialized view logs. How can Oracle quickly refresh multiple materialized views through a materialized view log? This article briefly describes the refresh principle.

 

 

 

 

First, let's take a look at the structure of the Materialized View:

 

SQL> create table t (id number, name varchar2 (30), num number );

 

The table has been created.

 

SQL> create materialized view log on t with rowid, sequence (id, name) including new values;

 

The materialized view log has been created.

 

SQL> desc mlog $ _ t
Is the name empty? Type
------------------------------------------------------------
ID NUMBER
NAME VARCHAR2 (30)
M_ROW $ VARCHAR2 (255)
SEQUENCE $ NUMBER
SNAPTIME $ DATE
DMLTYPE $ VARCHAR2 (1)
OLD_NEW $ VARCHAR2 (1)
CHANGE_VECTOR $ RAW (255)

 

ID and NAME are the columns in the base table specified when the materialized view log is created. They record the ID and NAME values corresponding to each DML operation.

 

M_ROW $ stores the ROWID information of the base table. You can locate the DML operation records based on the information in M_ROW $.

 

SEQUENCE $ records the SEQUENCE numbers based on the SEQUENCE of DML operations. When refreshing, the SEQUENCE in SEQUENCE $ can be consistent with the execution SEQUENCE in the base table.

 

The SNAPTIME $ Column records the refresh operation time.

 

The record values I, U, and D of DMLTYPE $ indicate whether the operation is INSERT, UPDATE, or DELETE.

 

OLD_NEW $ indicates whether the information stored in the materialized view log is the value before the DML operation (old value) or after the DML operation (new value ). In addition to the O and N types, the UPDATE operation may also be represented as U.

 

CHANGE_VECTOR $ record which or which fields the DML operation takes place.

 

For a detailed description of the log structure in the materialized view, refer:

 

Materialized view log structure: http://blog.itpub.net/post/468/20498

 

According to the above description, we can find that when refresh the materialized view, we only need to locate the base table record through M_ROW $ Based on the SEQUENCE given by the SEQUENCE $ column. If it is an UPDATE operation, locate the field through CHANGE_VECTOR $, and then repeat the DML operation based on the data in the base table.

 

If the materialized view log is only for one Materialized View, the refresh process is so simple. You still need to clear the materialized view log after refresh.

 

However, Oracle materialized view logs support quick refresh of multiple materialized views at the same time, that is, during refresh, the materialized view log records must be determined which are required for refresh the current materialized view and which are not required. Also, the materialized view must determine which records need to be cleared and which do not need to be cleared after the materialized view is refreshed.

 

Review the log structure of the materialized view and find that there is only one SHAPTIME $ Column left. How Does Oracle support multiple materialized views through this column alone? The following is a small example.

 

Use the table and materialized view logs created above to create three materialized views that are refreshed quickly.

 

SQL> create materialized view mv_t_id refresh fast
2 select id, count (*) from t group by id;

 

The materialized view has been created.

 

SQL> create materialized view mv_t_name refresh fast
2 select name, count (*) from t group by name;

 

The materialized view has been created.

 

SQL> create materialized view mv_t_id_name refresh fast
2 select id, name, count (*) from t group by id, name;

 

The materialized view has been created.

 

SQL> insert into t values (1, 'A', 2 );

 

One row has been created.

 

SQL> insert into t values (1, 'B', 3 );

 

One row has been created.

 

SQL> insert into t values (2, 'A', 5 );

 

One row has been created.

 

SQL> insert into t values (3, 'B', 7 );

 

One row has been created.

 

SQL> update t set name = 'C' where id = 3;

 

1 row updated.

 

SQL> delete t where id = 2;

 

One row has been deleted.

 

SQL> select id, name, m_row $, snaptime $, dmltype $ from mlog $ _ t;

 

Id name M_ROW $ SNAPTIME $ D
----------------------------------------------------------
1 a AAACJEAAFAAAAD4AAA 4000-01-01 00:00:00 I
1 B AAACJEAAFAAAAD4AAB 4000-01-01 00:00:00 I
2 a AAACJEAAFAAAAD4AAC 4000-01-01 00:00:00 I
3 B AAACJEAAFAAAAD4AAD 4000-01-01 00:00:00 I
3 B AAACJEAAFAAAAD4AAD 4000-01-01 00:00:00 U
3 c AAACJEAAFAAAAD4AAD 4000-01-01 00:00:00 U
2 a AAACJEAAFAAAAD4AAC 4000-01-01 00:00:00 D

 

You have selected 7 rows.

 

After a DML operation is performed, the value of SNAPTIME $ in the materialized view log is 4000-01-01 00:00:00. This value indicates that this record has not been refreshed by any materialized view. The first materialized view to refresh these records will update 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 is successfully completed.

 

SQL> select id, name, m_row $, snaptime $, dmltype $ from mlog $ _ t;

 

Id name M_ROW $ SNAPTIME $ D
----------------------------------------------------------
1 a AAACJEAAFAAAAD4AAA 00:56:59 I
1 B AAACJEAAFAAAAD4AAB 00:56:59 I
2 a AAACJEAAFAAAAD4AAC 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
3 c AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
2 a AAACJEAAFAAAAD4AAC 00:56:59 D

 

You have selected 7 rows.

 

Oracle knows that three materialized views are created on table T based on the information in the data dictionary. Therefore, after the MV_T_ID is refreshed, the materialized view record is not deleted.

The Oracle Data Dictionary also stores the last refresh time and current refresh status of each materialized view.

SQL> select name, last_refresh from user_mview_refresh_times;

 

NAME LAST_REFRESH
-------------------------------------------------
MV_T_ID 2005-03-06 00:56:59
MV_T_ID_NAME 2005-03-06 00:46:09
MV_T_NAME 2005-03-06 00:46:04

 

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

 

MVIEW_NAME LAST_REFRESH_DATE STALENESS
--------------------------------------------------------------------
MV_T_ID 2005-03-06 00:56:59 FRESH
MV_T_ID_NAME 2005-03-06 00:46:09 NEEDS_COMPILE
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE

 

These views record the last time each materialized view was refreshed and show whether the data in each materialized view was synchronized with the base table. The MV_T_ID has just been refreshed, so the status is FRESH. The other two are in the NEEDS_COMPILE status because the base table has been refreshed (created) and DML operations. If you perform DML operations on the base table, the MV_T_ID status also changes to NEEDS_COMPILE.

 

SQL> insert into t values (4, 'D', 10 );

 

One row has been created.

 

SQL> commit;

 

Submitted.

 

SQL> select id, name, m_row $, snaptime $, dmltype $ from mlog $ _ t;

 

Id name M_ROW $ SNAPTIME $ D
----------------------------------------------------------
1 a AAACJEAAFAAAAD4AAA 00:56:59 I
1 B AAACJEAAFAAAAD4AAB 00:56:59 I
2 a AAACJEAAFAAAAD4AAC 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
3 c AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
2 a AAACJEAAFAAAAD4AAC 00:56:59 D
4 d AAACJEAAFAAAAD4AAE 4000-01-01 00:00:00 I

 

Eight rows have been selected.

 

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

 

MVIEW_NAME LAST_REFRESH_DATE STALENESS
--------------------------------------------------------------------
MV_T_ID 2005-03-06 00:56:59 NEEDS_COMPILE
MV_T_ID_NAME 2005-03-06 00:46:09 NEEDS_COMPILE
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE

 

The following refresh the materialized view MV_T_ID_NAME. The refresh operation determines that only the records of the SNAPTIME $ column greater than the LAST_REFRESH_DATE column in the current materialized view are refreshed, because the value of SNAPTIME $ in all records in the materialized view log is larger than the time point at which MV_T_ID_NAME was last refreshed, all records are refreshed. For records whose SNAPTIME $ column value is 4000-01-01 00:00:00, the materialized view updates the value of the SNAPTIME $ column to the current refresh time, the original values of the updated SNAPTIME $ columns are retained.

 

SQL> exec dbms_mview.refresh ('mv _ T_ID_NAME ')

 

The PL/SQL process is successfully completed.

 

SQL> select id, name, m_row $, snaptime $, dmltype $ from mlog $ _ t;

 

Id name M_ROW $ SNAPTIME $ D
----------------------------------------------------------
1 a AAACJEAAFAAAAD4AAA 00:56:59 I
1 B AAACJEAAFAAAAD4AAB 00:56:59 I
2 a AAACJEAAFAAAAD4AAC 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
3 c AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
2 a AAACJEAAFAAAAD4AAC 00:56:59 D
4 d AAACJEAAFAAAAD4AAE 01:18:22 I

 

Eight rows have been selected.

 

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

 

MVIEW_NAME LAST_REFRESH_DATE STALENESS
--------------------------------------------------------------------
MV_T_ID 2005-03-06 00:56:59 NEEDS_COMPILE
MV_T_ID_NAME 2005-03-06 01:18:22 FRESH
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE

 

If you refresh the materialized view MV_T_ID again, only the SNAPTIME $ time point of the record with ID = 4 is greater than the time point of the previous refresh of MV_T_ID. Therefore, only this record is refreshed, the value of SNAPTIME $ is not changed.

SQL> exec dbms_mview.refresh ('mv _ T_ID ')

 

The PL/SQL process is successfully completed.

 

SQL> select id, name, m_row $, snaptime $, dmltype $ from mlog $ _ t;

 

Id name M_ROW $ SNAPTIME $ D
----------------------------------------------------------
1 a AAACJEAAFAAAAD4AAA 00:56:59 I
1 B AAACJEAAFAAAAD4AAB 00:56:59 I
2 a AAACJEAAFAAAAD4AAC 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 00:56:59 I
3 B AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
3 c AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
2 a AAACJEAAFAAAAD4AAC 00:56:59 D
4 d AAACJEAAFAAAAD4AAE 01:18:22 I

 

Eight rows have been selected.

 

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

 

MVIEW_NAME LAST_REFRESH_DATE STALENESS
--------------------------------------------------------------------
MV_T_ID 2005-03-06 01:25:30 FRESH
MV_T_ID_NAME 2005-03-06 01:18:22 FRESH
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE

 

So far, no materialized view logs have been cleared. In fact, every time a refresh is completed, materialized view logs attempt to delete useless materialized view logs. The deletion condition of log records in the materialized view is to delete the last refresh time when the SNAPTIME $ column is less than or equal to that of all materialized views in the base table. In the above example, because MV_T_NAME has not been refreshed, its LAST_REFRESH_DATE is smaller than the value of all records in the materialized view log. Therefore, the log records in the materialized view have not been cleared.

SQL> insert into t values (5, 'E', 2 );

 

One row has been created.

 

SQL> commit;

 

Submitted.

 

SQL> exec dbms_mview.refresh ('mv _ T_NAME ')

 

The PL/SQL process is successfully completed.

 

SQL> select id, name, m_row $, snaptime $, dmltype $ from mlog $ _ t;

 

Id name M_ROW $ SNAPTIME $ D
----------------------------------------------------------
5 e AAACJEAAFAAAAD4AAF 01:31:33 I

 

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

 

MVIEW_NAME LAST_REFRESH_DATE STALENESS
--------------------------------------------------------------------
MV_T_ID 2005-03-06 01:25:30 NEEDS_COMPILE
MV_T_ID_NAME 2005-03-06 01:18:22 NEEDS_COMPILE
MV_T_NAME 2005-03-06 01:31:33 FRESH

 

In the materialized view, MV_T_NAME refreshes each record in the materialized view, updates the SNAPTIME $ time of the record with ID = 5, and clears all other log records in the materialized view.

Finally, let's briefly summarize:

During refresh, The Materialized View refreshes all records whose SNAPTIME $ is later than the last refresh time of the materialized view, and updates all records whose values are 4000-01-01 00:00:00 to the current refresh time. For other records that are later than the last refresh time, only refresh is not changed. In this way, after the refresh is completed, the data dictionary records the last refresh Time of the current materialized view as the current time, this ensures that all records in the materialized view log are less than or equal to the refresh time. Therefore, each materialized view only needs to refresh the record later than the last refresh time and ensure that the time of all records is less than or equal to the last refresh time after each refresh. No matter how many materialized views, you can use the same materialized view log for quick refresh without affecting each other. After the materialized view is refreshed, the records whose SNAPTIME $ column is earlier than the last refresh time of all materialized views are cleared, and these records have been refreshed by all materialized views, it is meaningless to save the log in the materialized view.