Oracle 11g R2 materialized view Log new commit SCN statement overview

In 11.2, materialized view logs also add features that, for local materialized views, can now be refreshed using the commit SCN instead of the timestamp method in the previous version.

Look at a simple example:

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);

Table has been created.

Sql> CREATE materialized VIEW LOG on T with COMMIT SCN;

The materialized view log has been created.

Sql> INSERT into T

2 VALUES (1, ' TEST ', 18);

1 lines have been created.

Sql> SELECT * from mlog$_t;

ID D O change_vector$$ xid$$

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

1 I N FE 2.8148E+15

Unlike the ordinary timestamp materialized view, the materialized view log no longer records the timestamp column, but instead records the modified XID information and determines whether a materialized view needs to refresh the corresponding record through XID and SCN.

In this way, you can improve the performance of materialized view refreshes.

However, because the SCN can only be used locally, this materialized view log supports only local materialized views, while remote materialized views are not supported.

sql> CREATE DATABASE LINK test112@test112 USING ' 172.25.198.230/test112 ';

The database link has been created.

Sql> SELECT global_name from global_name@test112@test112;

Global_name

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

TEST112

Sql> CREATE materialized VIEW mv_t1

2 REFRESH FAST

3 as SELECT * from t@test112@test112;

CREATE materialized VIEW Mv_t1

*

Line 1th Error:

ORA-32415: Remote materialized views that can be quickly refreshed are not supported from tables with materialized view logs based on the submit SCN

Sql> CREATE materialized VIEW mv_t

2 REFRESH FAST

3 as SELECT * from T;

The materialized view was created.

Because there is currently only a 11.2 version of the database, in order to demonstrate remote access, created a database chain to the current database, you can see the establishment of a local materialized view no problem, but the remote materialized view can not be based on the SCN materialized view log.

In addition to creating a single table materialized view based on a primary key or ROWID, a local materialized view based on connection, aggregation, or union All can take advantage of the materialized view log of the commit SCN.

However, if the table contains LOB columns, the materialized view log error for the commit SCN is established:

sql> CREATE TABLE T_lob

2 (ID number PRIMARY KEY,

3 CONTENT CLOB);

Table has been created.

Sql> CREATE materialized VIEW LOG on T_lob with COMMIT SCN;

CREATE materialized VIEW LOG on T_lob with COMMIT SCN

*

Line 1th Error:

ORA-32421: Tables with LOB columns do not support materialized view logs based on the submit SCN

In addition, if the materialized view relies on multiple materialized view logs, these materialized views must either be based on timestamp or are based on the commit SCN, not the two mixed, or they will have an error:

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

sql> CREATE TABLE T1

2 (ID number PRIMARY KEY,

3 NAME VARCHAR2 (30));

Table has been created.

sql> CREATE TABLE T2

2 (ID number PRIMARY KEY,

3 FID number,

4 address VARCHAR2 (30));

Table has been created.

Sql> CREATE materialized VIEW LOG on T1 with ROWID (ID);

The materialized view log has been created.

Sql> CREATE materialized VIEW LOG on T2 with ROWID, COMMIT SCN (ID, FID);

The materialized view log has been created.

Sql> CREATE materialized VIEW mv_t12

2 REFRESH FAST

3 as SELECT T1. ROWID ROWID1, T2. ROWID ROWID2, T1.id ID1, T2.id ID2

4 from T1, T2

5 WHERE t1.id = T2. FID;

From T1, T2

*

Line 4th Error:

ORA-32414: Materialized view logs are incompatible and cannot be quickly refreshed

This is because the quick refresh of materialized view logs containing the commit SCN has changed.

Author: 51cto Blog Oracle Little Bastard