Oracle Fast Refresh method of materialized view logs based on commit SCN

The materialized view log in 11.2 can specify the commit SCN, when materialized view refreshes do not require a timestamp, a simple description of how Oracle implements the refresh through the commit SCN.

Establish a test environment:

Sql> SELECT * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

sql> CREATE TABLE T

2 (ID number PRIMARY KEY,

3 NAME VARCHAR2 (30),

4 Age Number (3));

Table has been created.

Sql> INSERT into T

2 SELECT rownum, Tname, rownum

3 from TAB;

18 lines have been created.

Sql> CREATE materialized VIEW LOG on T

2 with COMMIT SCN;

The materialized view log has been created.

Sql> CREATE materialized VIEW mv_t REFRESH FAST

2 as SELECT * from T;

The materialized view was created.

To modify the materialized View graph table:

Sql> INSERT into T

2 VALUES (' TEST ', 100);

1 lines have been created.

Sql> UPDATE T

2 SET NAME = ' TEST UPDATE '

3 WHERE ID = 1;

1 rows have been updated.

Sql> DELETE T

2 WHERE ID = 2;

1 rows have been deleted.

Sql> COMMIT;

Submit completed.

sql>colchange_vector$$ FORMAT A30

sql> SET NUMW 16

Sql> SELECT * from mlog$_t;

ID D O change_vector$$ xid$$

---------------- - - ------------------------------ ----------------

I N FE 2533498128696031

1 u u 04 2533498128696031

2 D O 00 2533498128696031

You can see that the INSERT, update, and delete operations are recorded in the materialized view log, and because the commit SCN is specified, the xid$$ column is out of the materialized view log and the snaptime$$ column is missing.

Sql> CREATE TABLE T_timestamp (ID number PRIMARY KEY);

Table has been created.

Sql> CREATE materialized VIEW LOG on T_timestamp;

The materialized view log has been created.

Sql> INSERT into T_timestamp VALUES (1);

1 lines have been created.

Sql> SELECT * from Mlog$_t_timestamp;

ID snaptime$$ D O change_vector$$ xid$$

---------------- -------------- - - ------------------------------ ----------------

1 January-January -00 I N FE 2814912975871092

As you can see, materialized view logs that do not specify the with COMMIT scn,11.2 also contain xid$$ columns, which are estimated to implement a unified interface.

The following is a trace of the refresh process to see how Oracle realizes the refresh:

Sql> ALTER session SET EVENTS ' 10046 TRACE NAME context FOREVER, Level 12 ';

The session has changed.

Sql> EXEC Dbms_mview. REFRESH (' mv_t ')

The PL/SQL process has completed successfully.

Sql> ALTER session SET EVENTS ' 10046 TRACE NAME context off ';

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/