Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the log structure of oracle materialized view". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Oracle materialized view log structure

The rapid refresh of materialized view requires that the materialized view log must be established. This article briefly describes the meaning and purpose of each field in the materialized view log.

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.

Materialized view logs can be created with a variety of options: you can specify ROWID, PRIMARY KEY, and OBJECT ID types, and you can also specify SEQUENCE or explicitly specify column names. The structure of the materialized view log resulting from all of the above situations is different.

Four columns that any materialized view will include:

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.

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.

The following is illustrated in detail by examples:

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 has been created

SQL > create table t_oid of t_object

The table has been created.

SQL > desc t_oid

Is the name empty? Types

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.

After setting up the environment, look at the fields contained in the materialized view log:

SQL > desc mlog$_t_rowid

Is the name empty? Types

NAME VARCHAR2 (30)

NUM NUMBER

Masked Rowling $VARCHAR2

SEQUENCE$$ NUMBER

SNAPTIME$$ DATE

DMLTYPE$$ VARCHAR2 (1)

OLD_NEW$$ VARCHAR2 (1)

CHANGE_VECTOR$$ RAW (255)

In addition to the most basic four columns, the materialized view log contains corresponding columns because ROWID, SEQUENCE, and NAME, NUM columns are specified.

SQL > desc mlog$_t_pk

Is the name empty? Types

ID NUMBER

SNAPTIME$$ DATE

DMLTYPE$$ VARCHAR2 (1)

OLD_NEW$$ VARCHAR2 (1)

CHANGE_VECTOR$$ RAW (255)

The materialized view log of the object table contains the system object identity column after it is established.

Primary key column, ROWID column, OBJECT ID column, SEQUENCE column and the column specified when creating the materialized view.

The primary key, ROWID, or OBJECT ID are used to uniquely represent the records in the materialized view log.

SEQUENCE numbers the records in the materialized view log according to the order in which the operations occur.

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

SQL > insert into t_pk values (1, 'asides, 5)

1 line has been created.

SQL > update t_pk set name ='c 'where id = 1

1 row has been updated.

SQL > delete t_pk

1 line 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, 'asides, 5)

1 line has been created.

SQL > update t_oid set name ='c 'where id = 1

1 row has been updated.

SQL > delete t_oid

1 line 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

The fallback is complete.

II. Time series

When the DML operation basically occurs, it is recorded in the materialized view log, and the specified time is 00:00:00 on January 1st, 4000. If the materialized view log is used by multiple materialized views, after a materialized view is refreshed, the time of its refresh record is updated to its refresh time.

Let's create two quickly refreshed materialized views to demonstrate the changes in the time column. The materialized view log can only be used by establishing a materialized view that is refreshed quickly. If only one materialized view is created, the materialized view log will be cleared when 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

A materialized view has been created.

SQL > create materialized view mv_t_rowid1 refresh fast as select name, count (*) from t_rowid group by name

A materialized view has been created.

SQL > insert into t_rowid values (1, 'asides, 5)

1 line has been created.

SQL > update t_rowid set name ='c 'where id = 1

1 row has been updated.

SQL > delete t_rowid

1 line 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

The submission is complete.

SQL > select snaptime$$ from mlog$_t_rowid

SNAPTIME$$

-

2012-5-23 15:41:41

2012-5-23 15:41:41

2012-5-23 15:41:41

2012-5-23 15:41:41

After COMMIT, the materialized view mv_t_rowid is refreshed, and the SNAPTIME$$ column is updated to its own refresh time.

Type of operation and new and old values

The operation type is relatively simple: only I (INSERT), D (DELETE) and U (UPDATE) are included.

There are also three kinds of new and old values: O represents the old value (usually the corresponding operation DELETE), N represents the new value (the general corresponding operation is INSERT), and a U (corresponding to the UPDATE operation).

SQL > insert into t_pk values (1, 'asides, 5)

1 line has been created.

SQL > insert into t_pk values (2, 'baked, 7)

1 line has been created.

SQL > insert into t_pk values (3, 'centering, 9)

1 line has been created.

SQL > update t_pk set name ='c 'where id = 1

1 row has been updated.

SQL > update t_pk set id = 4 where id = 2

1 row has been updated.

SQL > delete t_pk where id = 3

1 line 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 U

2 D O

4 I N

3 D O

Seven rows have been selected.

Three records are inserted first, followed by the UPDATE operation. It should be noted that for the primary key-based materialized view log, if the primary key is updated, the UPDATE operation is transformed into a DELETE operation, an INSERT operation. Finally, there 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, 'asides, 5)

1 line has been created.

SQL > insert into t_rowid values (2, 'baked, 7)

1 line has been created.

SQL > insert into t_rowid values (3, 'centering, 9)

1 line has been created.

SQL > update t_rowid set name ='c 'where id = 1

1 row has been updated.

SQL > update t_rowid set id = 4 where id = 2

1 row has been updated.

SQL > delete t_rowid where id = 3

1 line has been deleted.

SQL > select name, num, dmltype$$, old_new$$ from mlog$_t_rowid

NAME NUM Mangrowang $D O

--

A 5 AAACIDAAFAAAAD4AAC I N

B 7 AAACIDAAFAAAAD4AAA I N

C 9 AAACIDAAFAAAAD4AAB I N

A 5 AAACIDAAFAAAAD4AAC U U

C 5 AAACIDAAFAAAAD4AAC U N

B 7 AAACIDAAFAAAAD4AAA U U

B 7 AAACIDAAFAAAAD4AAA U N

C 9 AAACIDAAFAAAAD4AAB D O

Eight rows have been selected.

The query result is similar to the above, except that each UPDATE operation corresponds to two records in the materialized view log. The original record DMLTYPE$$ and OLD_NEW$$ corresponding to the UPDATE operation are both U, and a new record after the UPDATE operation is DMLTYPE$$. This occurs when the INCLUDING NEW VALUES statement is indicated when the materialized view log is created.

4. Modify vector

Finally, we briefly discuss the CHANGE_VECTOR$$ column.

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 CHANGE_VECTOR$$ corresponding to the INSERT operation is listed as full FF.

SQL > insert into t_rowid values (1, 'asides, 5)

1 line has been created.

SQL > insert into t_rowid values (2, 'baked, 7)

1 line has been created.

SQL > insert into t_rowid values (3, 'centering, 9)

1 line has been created.

SQL > update t_rowid set name ='c 'where id = 1

1 row has been updated.

SQL > update t_rowid set id = 4 where id = 2

1 row has been updated.

SQL > update t_rowid set name = 'dwells, num = 11 where id = 3

1 row has been updated.

SQL > delete t_rowid where id = 3

1 line has been deleted.

SQL > select name, num, dmltype$$, old_new$$, change_vector$$ from mlog$_t_rowid

As you can see, as analyzed above, the INSERT is 00, the update for the first column is 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 a TRUNCATE is performed on the MLOG$ table, or the materialized view log is rebuilt, it may offset the start of the first column.

SQL > insert into t_pk values (1, 'asides, 5)

1 line has been created.

SQL > insert into t_pk values (2, 'baked, 7)

1 line has been created.

SQL > insert into t_pk values (3, 'centering, 9)

1 line has been created.

SQL > update t_pk set name ='c 'where id = 1

1 row has been updated.

SQL > update t_pk set id = 4 where id = 2

1 row has been updated.

SQL > delete t_pk where id = 1

1 line has been deleted.

SQL > commit

The submission is complete.

SQL > select * from mlog$_t_pk

This result is basically the same as the ROWID type, except that if the primary key is updated, the UPDATE operation will be recorded as a DELETE and an INSERT in the materialized view log, but in this case the value of the CHANGE_VECTOR$$ for INSERT is FF.

SQL > insert into t_oid values (1, 'asides, 5)

1 line has been created.

SQL > update t_oid set name ='c 'where id = 1

1 row has been updated.

SQL > update t_oid set id = 5 where id = 1

1 row has been updated.

SQL > delete t_oid

1 line has been deleted.

SQL > commit

The submission is complete.

SQL > select * from mlog$_t_oid

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

NAME SEGCOLLENGTH

SYS_NC_OID$ 16

SYS_NC_ROWINFO$ 1

ID 22

NAME 30

NUM 22

This result is also basically consistent with the ROWID type, and it is important to note that because the object table contains two implicit columns, ID is no longer the first field, but the third, so 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)

1 line has been created.

SQL > update t set col1 = 10

1 row has been updated.

SQL > update t set col11 = 110,

1 row has been updated.

SQL > update t set col5 = 50, col12 = 120

1 row has been updated.

SQL > delete t

1 line has been deleted.

SQL > commit

The submission is complete.

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.

This is the end of the content of "what is the log structure of oracle materialized view". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.