Implementation of Materialized View in OCM Examination

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,

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,

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.

For more information about Oracle, see the Oracle topic page? Tid = 12

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.

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
Select id, count (*) from t group by id;

The materialized view has been created.

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

The materialized view has been created.

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

The materialized view has been created.

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

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

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

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

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

SQL> delete t where id = 2;

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.