Shulou(Shulou.com)06/01 Report--

1. What is the materialized view log

The rapid refresh of materialized view of oracle requires that the materialized view log must be established, and the incremental refresh function can be realized through the materialized view log.

The definition of the materialized view log given in the official documentation:

A materialized view log is required on a master to perform a fast refresh on materialized views based on the master. When you create a materialized view log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log. A materialized view log can hold the primary keys, rowids, or object identifiers of rows, or both, that have been updated in the master table or master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.

The name of a materialized view log's table is MLOG$_master_name. The materialized view log is created in the same schema as the target master. One materialized view log can support multiple materialized views on its master table or master materialized view. As described in the previous section, the internal trigger adds change information to the materialized view log whenever a DML transaction has taken place on the target master.

Materialized view logs can be created with a variety of options: you can specify ROWID, PRIMARY KEY, and OBJECTID types, and you can also specify SEQUENCE or explicitly specify column names. However, the structure of the materialized view log resulting from all of the above situations is different. It should be noted that when a DML operation occurs, the internal trigger records the changes in the materialized view log, that is, the materialized view does not support DDL synchronization, so the form of select * from cannot be used in the writing process of the materialized view, because when the base table changes, the materialized view will become invalid.

The name of the materialized view log is MLOG$_ followed by the name of the base table. If the length of the table name is more than 20 digits, only the first 20 digits are taken. When the name is duplicated after truncation, Oracle will automatically add a number to the materialized view log name as the serial number.

Although materialized views may have different formats, any materialized view will include something like the following:

The following is a materialized view log of primarykey:

Zx@ORA11G > desc mlog$_employees Name Null? Type-EMPLOYEE_ID NUMBER (6) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2 (1) OLD_NEW$$ VARCHAR2 (1) CHANGE_VECTOR$$ RAW (255) XID$$ NUMBER

The relevant explanations are as follows:

SNAPTIME$$: is used to indicate the refresh time.

DMLTYPE$$: is used to indicate the type of DML operation, and I represents INSERT,D for DELETE,U and UPDATE.

OLD_NEW$$: is used to indicate whether the value is new or old. N (EW) represents the new value, O (LD) represents the old value, and U represents the UPDATE operation.

CHANGE_VECTOR$$: represents a modification vector, which is used to indicate which field or fields are being modified.

Both INSERT and DELETE operations are recordset, that is, INSERT and DELETE affect the entire record. While the UPDATE operation is a set of fields, the UPDATE operation may update all fields of the entire record, or only individual fields.

Whether in terms of performance or data consistency, materialized views should be refreshed based on field sets. Oracle uses the CHANGE_VECTOR$$ column to record which fields have changed for each record.

Materialized view logs based on primary key, ROWID, and OBJECT ID are slightly different on CHANGE_VECTOR$$, but the overall design idea is the same.

CHANGE_VECTOR$$ columns are of type RAW. In fact, Oracle uses each BIT bit to map a column.

For example, the first column is updated to 02, or 00000010. The second column is set to 04, or 00000100, and the third column is set to 08, or 00001000. When the first column and the second column are updated at the same time, it is set to 0600000110. If all three columns are updated, set to 0Eminute 00001110.

And so on, column 4 is updated as 10, column 5 20, column 6 40, column 7 80, column 8 0001. When the 1000th column is updated, the length of the CHANGE_VECTOR$$ is 1000 and the length is 252.

In addition to fields that can represent UPDATE, you can also represent INSERT and DELETE. The DELETE operation CHANGE_VECTOR is listed as all zero, and the specific number is determined by the number of columns in the base table. The lowest bit of the INSERT operation is FE. If there are a large number of columns in the base table and there are high bits, all the high bits are FF. If the INSERT operation is caused by the UPDATE operation updating the primary key as discussed earlier, the CHANGEVECTOR corresponding to the INSERT operation is listed as full FF.

If the WITH is followed by ROWID, the materialized view log contains: ROWID: the ROWID used to store the changed records.

If WITH is followed by PRIMARY KEY, the materialized view log contains primary key columns.

If WITH is followed by OBJECT ID, the materialized view log contains the object ID that SYS_NC_OID$: uses to record each change object.

If WITH is followed by SEQUENCE, the materialized view day will include: SEQUENCE$$: gives each operation a SEQUENCE number, which ensures that the refresh will be done in order.

If the WITH is followed by one or more COLUMN names, these columns are included in the materialized view log.

Second, quickly refresh the data process according to the materialized view log

2.1 create a test environment

Zx@ORA11G > create table t (id number,name varchar2 (10), address varchar2 (10)); Table created.zx@ORA11G > create materialized view log on t with rowid,sequence (id,name) including new values;Materialized view log created.zx@ORA11G > desc mlog$_t Name Null? Type-ID NUMBER NAME VARCHAR2 (10) Maurerowski $VARCHAR2 (255) SEQUENCE$$ NUMBER SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2 (1) OLD_NEW$$ VARCHAR2 (1) CHANGE_VECTOR$$ RAW (255) XID$$ NUMBER

ID and NAME are the columns in the base table specified when the materialized view log is created, and they record the values of ID and NAME for each DML operation.

DML operations: save the ROWID information of the base table, and you can locate the record where the DML operation occurred based on the information in MacroWare $.

SEQUENCE$$: records the number of the sequence according to the order in which the DML operation occurs, and when refreshed, it can be consistent with the execution order in the base table according to the order in the SEQUENCE.

The SNAPTIME$$: column records the time of the refresh operation.

The record values I, U, and D of DMLTYPE$$: indicate whether the operation is 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 after the DML operation (the new value). In addition to the O and N types, for UPDATE operations, it may also be represented as U.

The CHANGE_VECTOR$$: record DML operation occurs on that field or fields

When refreshing the materialized view, you only need to navigate to the record of the base table through the UPDATE operation according to the order given by the SEQUENCE column. If it is a UPDATE operation, locate the field through CHANGE_VECTOR$$, and then repeat the DML operation according to the data in the base table.

If the materialized view log is for only one materialized view, then the refresh process is that simple, and all you need to do is clear 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 to say, when the materialized view is refreshed, it must also determine which materialized view log records are needed for the current materialized view refresh and which are not. Moreover, the materialized view must also determine which records in the materialized view log need to be cleared and which do not need to be cleared after the materialized view is refreshed.

Looking back at the structure of the materialized view log, it is found that there is only one SHAPTIME$$ column left, so how can Oracle support multiple materialized views through this column alone?

2.2 create a small example to illustrate:

Using the table and materialized view log established above, let's create three quickly refreshed materialized views for this table and DML the t table:

Zx@ORA11G > create materialized view mv_t_id 2 refresh fast 3 as select id,count (*) 4 from t 5 group by id;Materialized view created.zx@ORA11G > create materialized view mv_t_name 2 refresh fast 3 as select name,count (*) 4 from t 5 group by name;Materialized view created.zx@ORA11G > create materialized view mv_t_both 2 refresh fast 3 as select id,name,count (*) 4 from t 5 group by id,name;Materialized view created.zx@ORA11G > insert into t values (1, 'zx',' hb') 1 row created.zx@ORA11G > insert into t values (2, 'wl',' sd'), 1 row created.insert into t values (3, 'yc',' bj'), 1 row created.zx@ORA11G > update t set address = 'bj_cp' where id = 3 row updated.zx@ORA11G > delete from t where id = 2 bj_cp' where id 1 row deleted.zx@ORA11G > commit;Commit complete.

Query the materialized view log. You can view the corresponding log for each dml operation.

Zx@ORA11G > col MacroWare $for a30zx@ORA11G > col change_vector$$ for a30zx@ORA11G > set num 20zx@ORA11G > select * from mlog$_t ID NAME massively $SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$ -1 zx AAAVs6AAEAAAAJVAAA 8 40000101 00:00:00 I N FE 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 9 40000101 00:00:00 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 10 40000101 00:00:00 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 11 40000101 00:00:00 U U 08 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 12 40000101 00:00:00 U N 08 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 13 40000101 00:00:00 D O 00 28148829110934596 rows selected.

When the DML operation occurs, the SNAPTIME$$ column in the materialized view log maintains a value of 40000101 00:00:00. This value indicates that the record has not been refreshed by any materialized view. The first materialized view that refreshes these records updates the value of SNAPTIME$$ to the current refresh time of the materialized view.

Refresh a materialized view and view the materialized view log again:

Zx@ORA11G > exec dbms_mview.refresh ('MV_T_ID'); PL/SQL procedure successfully completed.zx@ORA11G > select * from mlog$_t ID NAME massively $SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$ -1 zx AAAVs6AAEAAAAJVAAA 8 20170809 15:58:30 I N FE 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 9 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 10 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 11 20170809 15:58:30 U U 08 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 12 20170809 15:58:30 U N 08 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 13 20170809 15:58:30 D O 00 28148829110934596 rows selected.

According to the information in the data dictionary, Oracle can know that three materialized views have been established on table T. therefore, after MV_T_ID refreshes, the materialized view record will not be deleted. However, the time corresponding to the SNAPTIME$$ column is changed to the time when the MV_T_ID materialized view is refreshed

The data dictionary of Oracle also stores the last refresh time and current refresh state of each materialized view.

Zx@ORA11G > select name,master,last_refresh from user_mview_refresh_times NAME MASTER LAST_REFRESH-- MV_T_BOTH T 20170809 15:45:10MV_T_ID T 20170809 15:58:30MV_T_NAME T 20170809 15:45:05zx@ORA11G > select mview_name Last_refresh_date, staleness from user_mviews MVIEW_NAME LAST_REFRESH_DATE STALENESS-- MV_T_BOTH 20170809 15:45:10 NEEDS_COMPILEMV_T_ID 20170809 15:58:30 FRESHMV_T_NAME 20170809 15:45:05 NEEDS_COMPILE

These views record the time when each materialized view last performed a refresh operation, and whether the data in each materialized view is synchronized with the base table. Because MV_T_ID has just been refreshed, the state is FRESH, while the other two are NEEDS_COMPILE because the base table is DML after the refresh (establishment). If the DML operation is performed on the base table at this time, the state of the MV_T_ID will also become NEEDS_COMPILE.

Zx@ORA11G > insert into t values (4, 'zf',' sd'); 1 row created.zx@ORA11G > commit;Commit complete.zx@ORA11G > select mview_name,last_refresh_date, staleness from user_mviews MVIEW_NAME LAST_REFRESH_DATE STALENESS-- MV_T_BOTH 20170809 15:45:10 NEEDS_COMPILEMV_T_ID 20170809 15:58:30 NEEDS_COMPILEMV_T_NAME 20170809 15:45:05 NEEDS_COMPILEzx@ORA11G > select * from mlog$_t ID NAME massively $SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$ -1 zx AAAVs6AAEAAAAJVAAA 8 20170809 15:58:30 I N FE 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 9 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 10 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 11 20170809 15:58:30 U U 08 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 12 20170809 15:58:30 U N 08 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 13 20170809 15:58:30 D O 00 2814882911093459 4 zf AAAVs6AAEAAAAJVAAD 14 40000101 00:00 : 00 I N FE 844463584838593

Next, refresh the materialized view MV_T_NAME. The refresh operation is based on the fact that only the records whose SNAPTIME$$ column is larger than the LAST_REFRESH_DATE of the current materialized view are refreshed. Since the SNAPTIME$$ values of all records in the materialized view log are larger than the last time the materialized view MV_T_ID_NAME was refreshed, all records will be refreshed. For records where the value of the SNAPTIME$$ column is 40000101 00:00:00, the materialized view updates the value of the SNAPTIME$$ column to the current refresh time, and maintains the original value for those SNAPTIME$$ columns that have already been updated.

Zx@ORA11G > exec dbms_mview.refresh ('MV_T_NAME'); PL/SQL procedure successfully completed.zx@ORA11G > select mview_name,last_refresh_date, staleness from user_mviews MVIEW_NAME LAST_REFRESH_DATE STALENESS-- MV_T_BOTH 20170809 15:45:10 NEEDS_COMPILEMV_T_ID 20170809 15:58:30 NEEDS_COMPILEMV_T_NAME 20170809 16:16:01 FRESHzx@ORA11G > select * from mlog$_t ID NAME massively $SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$ -1 zx AAAVs6AAEAAAAJVAAA 8 20170809 15:58:30 I N FE 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 9 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 10 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 11 20170809 15:58:30 U U 08 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 12 20170809 15:58:30 U N 08 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 13 20170809 15:58:30 D O 00 2814882911093459 4 zf AAAVs6AAEAAAAJVAAD 14 20170809 16:16 : 01 I N FE 8444635848385937 rows selected.

If the materialized view MV_T_ID is refreshed again at this time, only the time point of the SNAPTIME$$ of this record of ID=4 is greater than that of the last refresh of MV_T_ID, so only this record is refreshed without changing the value of SNAPTIME$$.

Zx@ORA11G > exec dbms_mview.refresh ('MV_T_ID'); PL/SQL procedure successfully completed.zx@ORA11G > select mview_name,last_refresh_date, staleness from user_mviews MVIEW_NAME LAST_REFRESH_DATE STALENESS-- MV_T_BOTH 20170809 15:45:10 NEEDS_COMPILEMV_T_ID 20170809 16:17:43 FRESHMV_T_NAME 20170809 16:16:01 FRESHzx@ORA11G > select * from mlog$_t ID NAME massively $SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$ -1 zx AAAVs6AAEAAAAJVAAA 8 20170809 15:58:30 I N FE 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 9 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 10 20170809 15:58:30 I N FE 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 11 20170809 15:58:30 U U 08 2814882911093459 3 yc AAAVs6AAEAAAAJVAAC 12 20170809 15:58:30 U N 08 2814882911093459 2 wl AAAVs6AAEAAAAJVAAB 13 20170809 15:58:30 D O 00 2814882911093459 4 zf AAAVs6AAEAAAAJVAAD 14 20170809 16:16 : 01 I N FE 844463584838593

So far, we have not seen the cleaning of the materialized view log. In fact, every time the materialized view log is refreshed, the materialized view log will try to delete the useless materialized view log records. The condition for deleting materialized view log records is to delete those SNAPTIME$$ columns that are less than or equal to the last refresh time of all materialized views in the base table. In the above example, because the MV_T_BOTH has not been refreshed, its LAST_REFRESH_DATE is smaller than all the recorded values in the materialized view log, so the materialized view log record cleanup has not occurred.

Zx@ORA11G > insert into t values (5, 'zq',' jx'); 1 row created.zx@ORA11G > commit;Commit complete.zx@ORA11G > exec dbms_mview.refresh ('MV_T_BOTH'); PL/SQL procedure successfully completed.zx@ORA11G > select mview_name,last_refresh_date, staleness from user_mviews MVIEW_NAME LAST_REFRESH_DATE STALENESS-- MV_T_BOTH 20170809 16:19:51 FRESHMV_T_ID 20170809 16:17:43 NEEDS_COMPILEMV_T_NAME 20170809 16:16:01 NEEDS_COMPILEzx@ORA11G > select * from mlog$_t ID NAME massively $SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$ XID$$ -5 zq AAAVs6AAEAAAAJVAAE 15 20170809 16:19:51 I N FE 2251898597934032

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

Summary:

When the materialized view is refreshed, all records whose SNAPTIME$$ is greater than the last refresh time of this materialized view are refreshed, and all records that are 40000101 00:00:00 are updated to the current refresh time. For other records that are larger than the last refresh time, only refresh does not change. In this way, when the refresh is completed, the last refresh time of the current materialized view recorded in the data dictionary is the current time, which ensures that all the current records in the materialized view log are less than or equal to the refresh time. Therefore, as long as each materialized view refreshes records greater than the last refresh time, and ensures that after each refresh, the time of all records is less than or equal to the last refresh time, then no matter how many materialized views there are, you can quickly refresh it using the same materialized view log without affecting each other. When the materialized view is refreshed, it clears the records in which the SNAPTIME$$ column is less than the last refresh time of all materialized views, and these records have been refreshed by all materialized views, so it is no longer meaningful to save them in the materialized view log.

Reference: http://blog.csdn.net/tianlesoftware/article/details/7720580

Http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#i30732

Http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#SQLRF01303

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.