Oracle materialized view log structure

Oracle materialized view log structure

To refresh a materialized view quickly, you must create a materialized view log. This article briefly describes the meaning and usage of each field in the materialized view log.

The name of the materialized view log is the name of the base table behind MLOG $ _. If the table name length exceeds 20 bits, only the first 20 bits are used. When the names are duplicated after the table is truncated, oracle automatically adds a number to the materialized view log name as the serial number.

Materialized View logs are created with multiple options: You can specify ROWID, primary key, and object id. You can also specify SEQUENCE or explicitly specify column names. The structure of Materialized View logs generated in the above situations is different.

Any materialized view includes four columns:
SNAPTIME $: indicates the refresh time.
DMLTYPE $: indicates the DML operation type, I indicates INSERT, D Indicates DELETE, and u indicates UPDATE.
OLD_NEW $: indicates whether the value is a new value or an old value. N (EW) indicates the new value, O (LD) indicates the old value, and u indicates the UPDATE operation.
CHANGE_VECTOR $: indicates which or which fields are modified.
If ROWID is followed by WITH, the materialized view log contains: M_ROW $: The ROWID used to store the changed records.
If the WITH clause is followed by the primary key, the materialized view log contains the primary key column.
If the object id is followed by WITH, the materialized view log contains: SYS_NC_OID $: the object id used to record each change OBJECT.
If sequence is followed by with, the materialized view date will include: sequence $: to give each operation a sequence number, so as to ensure that the refresh is performed in order during refresh.
If with is followed by one or more column names, the materialized view log contains these columns.

The following is an example:

SQL> Create Table t_rowid (ID number, name varchar2 (30), num number );
The table has been created.

SQL> Create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
The materialized view log has been created.

SQL> Create Table t_pk (ID number primary key, name varchar2 (30), num number );
The table has been created.

SQL> create materialized view log on t_pk with primary key;
The materialized view log has been created.

SQL> create type t_object as object (id number, name varchar2 (30), num number );
/
Type created

SQL> create table t_oid of t_object;
The table has been created.

SQL> desc t_oid;
Is the name empty? Type
----------------------------------------------------------------
ID NUMBER
NAME VARCHAR2 (30)
Num number

SQL> Create materialized view log on t_oid with Object ID;
The materialized view log has been created.

Create an environment and then check the fields contained in the materialized view log:
SQL> DESC mlog $ _ t_rowid;
Is the name empty? Type
--------------------------------------------------------------
NAME VARCHAR2 (30)
NUM NUMBER
M_ROW $ VARCHAR2 (255)
Sequence $ number
Snaptime $ date
Dmltype $ varchar2 (1)
OLD_NEW $ VARCHAR2 (1)
CHANGE_VECTOR $ RAW (255)
Besides the four most basic columns, the materialized view log contains the corresponding columns because the ROWID, SEQUENCE, NAME, and NUM columns are specified.

SQL> desc mlog $ _ t_pk;
Is the name empty? Type
-------------------------------------------------------------
ID NUMBER
SNAPTIME $ DATE
DMLTYPE $ VARCHAR2 (1)
OLD_NEW $ VARCHAR2 (1)
CHANGE_VECTOR $ RAW (255)

After the materialized view log of the object table is created, it contains the system object identification column.
1. Primary Key column, ROWID column, object id column, SEQUENCE column, and column specified when the materialized view is created.
Primary Key, ROWID, or object id are used to uniquely represent records in the materialized view log.
Sequence identifies the records in the materialized view logs based on the Operation Sequence.

The columns specified when the materialized view is created are recorded in the materialized view log.

SQL> insert into t_pk values (1, 'A', 5 );
One row has been created.

SQL> Update t_pk set name = 'C' where id = 1;
1 row updated.

SQL> Delete t_pk;
One row has been deleted.

SQL> select ID, dmltype $ from mlog $ _ t_pk;
Id d
-----------
1 I
1 U
1 D

SQL> insert into t_oid values (1, 'A', 5 );
One row has been created.

SQL> update t_oid set name = 'C' where id = 1;
1 row updated.

SQL> delete t_oid;
One row has been deleted.

SQL> select sys_nc_oid $, dmltype $ from mlog $ _ t_oid;
SYS_NC_OID $ D
---------------------------------
18DCFDE5D65B4D5A88602D6C09E5CE20 I
18DCFDE5D65B4D5A88602D6C09E5CE20 U
18DCFDE5D65B4D5A88602D6C09E5CE20 D

SQL> rollback;

Rollback completed.

Ii. Time column
When a DML operation occurs, it is recorded in the materialized view log. The specified time is 00:00:00, January 1, January 1, 4000. If the materialized view log is used by multiple materialized views, the record time refreshed by a materialized view is updated to the refresh time.
The following two materialized views are created to demonstrate the changes in the Time column. (Only materialized views that are quickly refreshed can use materialized view logs. If only one materialized view is created, logs of materialized views are cleared after the materialized view is refreshed.

SQL> create materialized view mv_t_rowid refresh fast on commit as select name, count (*) from t_rowid group by name;
The materialized view has been created.

SQL> create materialized view mv_t_rowid1 refresh fast as select name, count (*) from t_rowid group by name;
The materialized view has been created.

SQL> insert into t_rowid values (1, 'A', 5 );
One row has been created.

SQL> update t_rowid set name = 'C' where id = 1;
1 row updated.

SQL> delete t_rowid;
One row has been deleted.

SQL> select snaptime $ from mlog $ _ t_rowid;
SNAPTIME $
-------------------
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00

SQL> commit;
Submitted.

SQL> select snaptime $ from mlog $ _ t_rowid;
SNAPTIME $
-------------------
15:41:41
15:41:41
15:41:41
15:41:41
After COMMIT, the materialized view mv_t_rowid is refreshed, And the SNAPTIME $ column is updated to its own refresh time.

Iii. Operation Type and new and old values
The operation type is relatively simple: it only includes I (INSERT), D (DELETE), and U (UPDATE.
There are also three types of new and old values: O indicates the old value (usually the corresponding operation is DELETE) and N indicates the new value (generally the corresponding operation is INSERT ), there is also a U (corresponding to the UPDATE operation ).

SQL> insert into t_pk values (1, 'A', 5 );
One row has been created.

SQL> insert into t_pk values (2, 'B', 7 );
One row has been created.

SQL> insert into t_pk values (3, 'C', 9 );
One row has been created.

SQL> update t_pk set name = 'C' where id = 1;
1 row updated.

SQL> update t_pk set id = 4 where id = 2;
1 row updated.

SQL> delete t_pk where id = 3;
One row has been deleted.

SQL> select id, dmltype $, old_new $ from mlog $ _ t_pk;
ID D O
------------
1 I N
2 I N
3 I N
1 U
2 D O
4 I N
3 D O
You have selected 7 rows.

At the beginning, three records are inserted, followed by the UPDATE operation. Note that for Materialized View logs based on primary keys, if the primary key is updated, the UPDATE operation is converted to a DELETE operation and an INSERT operation. The last is the DELETE operation.

SQL> drop materialized view log on t_rowid;
The materialized view log has been deleted.

SQL> create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
The materialized view log has been created.

SQL> insert into t_rowid values (1, 'A', 5 );
One row has been created.

SQL> insert into t_rowid values (2, 'B', 7 );
One row has been created.

SQL> insert into t_rowid values (3, 'C', 9 );
One row has been created.

SQL> update t_rowid set name = 'C' where id = 1;
1 row updated.

SQL> update t_rowid set id = 4 where id = 2;
1 row updated.

SQL> delete t_rowid where id = 3;
One row has been deleted.

SQL> select name, num, m_row $, dmltype $, old_new $ from mlog $ _ t_rowid;
Name num M_ROW $ D O
----------------------------------------
A 5 AAACIDAAFAAAAD4AAC I N
B 7 AAACIDAAFAAAAD4AAA I N
C 9 aaacidaafaaaad4aab I n
A 5 aaacidaafaaaad4aac u
C 5 aaacidaafaaaad4aac u n
B 7 aaacidaafaaaad4aaa u
B 7 aaacidaafaaaad4aaa u n
C 9 aaacidaafaaaad4aab d o

Eight rows have been selected.

The query result is similar to the preceding one. The only difference is that each UPDATE operation corresponds to two records in the materialized view log. DMLTYPE $ and OLD_NEW $ are both U records corresponding to the UPDATE operation, and a new record corresponding to the UPDATE operation. DMLTYPE $ is U and OLD_NEW $ is N. This situation occurs when the including new values statement is pointed out when the materialized view log is created.

4. Modify the Vector
Finally, let's briefly discuss the CHANGE_VECTOR $ column.
INSERT and DELETE operations are all set of records, that is, INSERT and DELETE operations affect the entire record. The UPDATE operation is a field set. The UPDATE operation may UPDATE all fields of the entire record, or UPDATE only individual fields.
Whether in terms of performance or data consistency, the refresh of materialized views should be based on field sets.Oracle records the fields that change each record through the CHANGE_VECTOR $ column.

Materialized View logs based on primary key, ROWID, and object id are slightly different on CHANGE_VECTOR $, but the overall design concept is the same.

CHANGE_VECTOR $ the column type is RAW. In fact, Oracle uses a method to map a column with each BIT.

For example, the first column is updated to 02, that is, 00000010. The second column is set to 04, that is, 00000100, and the third column is set to 08, that is, 00001000. When the first and second columns are updated at the same time, the value is set. If all three columns are updated, set them to 0E, 00001110.
So far, when the 4th columns are updated, the values are 10, 5th columns 20, 6th columns 40, 7th columns 80, and 8th columns 0001. When the 1,000th column is updated, the length of CHANGE_VECTOR $ is 1000/4 + 2 and 252.
In addition to the UPDATE field, the INSERT and DELETE fields can also be used. DELETE operation CHANGE_VECTOR $ all columns are 0. The number of columns is determined by the number of columns in the base table. If the number of columns in the base table is large and the number of columns in the base table is high, all the values are FF. If the INSERT operation is caused by the UPDATE operation updating the primary key, the CHANGE_VECTOR $ column corresponding to the INSERT operation is full FF.

SQL> insert into t_rowid values (1, 'A', 5 );
One row has been created.

SQL> insert into t_rowid values (2, 'B', 7 );
One row has been created.

SQL> insert into t_rowid values (3, 'C', 9 );
One row has been created.

SQL> update t_rowid set name = 'C' where id = 1;
1 row updated.

SQL> update t_rowid set id = 4 where id = 2;
1 row updated.

SQL> update t_rowid set name = 'D', num = 11 where id = 3;
1 row updated.

SQL> delete t_rowid where id = 3;
One row has been deleted.
 
SQL> select name, num, m_row $, dmltype $, old_new $, change_vector $ from mlog $ _ t_rowid;
 
As shown in the preceding analysis, INSERT is FE, DELETE is 00, the first column is updated to 02, the second column is 04, and the second and third columns are updated to 0C. Note that normally, the first column starts at 02, but if the TRUNCATE operation is performed on the MLOG $ table, or the materialized view log is rebuilt, the start position of the first column may be offset.

SQL> insert into t_pk values (1, 'A', 5 );
One row has been created.

SQL> insert into t_pk values (2, 'B', 7 );
One row has been created.

SQL> insert into t_pk values (3, 'C', 9 );
One row has been created.

SQL> update t_pk set name = 'C' where id = 1;
1 row updated.

SQL> update t_pk set id = 4 where id = 2;
1 row updated.

SQL> delete t_pk where id = 1;
One row has been deleted.

SQL> commit
Submitted.

SQL> select * From mlog $ _ t_pk;

The results are basically the same as those of the rowid type. The difference is that if the primary key is updated, the update operation is recorded as a delete and an insert in the materialized view log, however, the value of change_vector $ corresponding to insert is ff.

SQL> insert into t_oid values (1, 'A', 5 );
One row has been created.

SQL> Update t_oid set name = 'C' where id = 1;
1 row updated.

SQL> Update t_oid Set ID = 5 where id = 1;
1 row updated.

SQL> Delete t_oid;
One row has been deleted.

SQL> commit;
Submitted.

SQL> select * From mlog $ _ t_oid;

SQL> select name, segcollength from sys. col $ where obj # = (select object_id from user_objects where object_name = 't_ OID ');

NAME SEGCOLLENGTH
------------------------------------------
SYS_NC_OID $16
SYS_NC_ROWINFO $1
ID 22
Name 30
Num 22

This result is basically the same as that of the rowid type. Note that because the object table contains two implicit columns, ID is no longer the first field, but the third one, therefore, the corresponding value is 08.

SQL> Create Table T (
Col1 number,
Col2 number,
Col3 number,
Col4 number,
Col5 number,
Col6 number,
Col7 number,
Col8 number,
Col9 number,
Col10 number,
Col11 number,
Col12 number
);
The table has been created.

SQL> create materialized view log on t with rowid;
The materialized view log has been created.

SQL> insert into t values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 );
One row has been created.

SQL> update t set col1 = 10;
1 row updated.

SQL> update t set col11 = 110;
1 row updated.

SQL> update t set col5 = 50, col12 = 120;
1 row updated.

SQL> delete t;
One row has been deleted.

SQL> commit;
Submitted.

SQL> select * from mlog $ _ t;

Finally, let's look at an example with a large number of columns. The only thing to note is that the low position is on the left and the high position is on the right.