oracle物化檢視日誌系列(二)

with子句操作限制
Restrictions on the WITH Clause This clause is subject to the following restrictions:
只能指定pk,object id,sequence,rowid其中一個
You can specify only one PRIMARY KEY, one ROWID, one OBJECT ID, one SEQUENCE, and one
column list for each materialized view log.
 
pk列隱式記錄在物化檢視日誌中,因此,如果column包含主鍵列之一,不能指定如下的組合
Primary key columns are implicitly recorded in the materialized view log. Therefore, you
cannot specify any of the following combinations if column contains one of the primary key columns:
 WITH ... PRIMARY KEY ... (column)
WITH ... (column) ... PRIMARY KEY
WITH (column)

NEW VALUES Clause
此子句可控制是否在物化檢視日誌中記錄dml變化前後的值
The NEW VALUES clause lets you determine whether Oracle Database saves both old and new values for update
DML operations in the materialized view log.
including指示在物化檢視日誌中包含新舊值,如果物化檢視日誌用於單表聚集物化檢視,為了增量重新整理必須指定including
INCLUDING Specify INCLUDING to save both new and old values in the log. If this log is for a table on which
you have a single-table materialized aggregate view, and if you want the materialized view to be eligible
for fast refresh, then you must specify INCLUDING.

而excluding指明在物化檢視日誌中不記錄新值.此為預設配置.可起到減少記錄新值的成本.如果想在基於單表
聚集物化檢視實現增量重新整理,不要使用此子句
EXCLUDING Specify EXCLUDING to disable the recording of new values in the log. This is the default.
You can use this clause to avoid the overhead of recording new values. Do not use this clause if you
have a fast-refreshable single-table materialized aggregate view defined on the master table.

Specifying Join Columns for Materialized View Logs: Example The following statement creates a materialized
view log on the order_items table of the sample oe schema. The log records primary keys and product_id,
which is used as a join column in "Creating a Fast Refreshable Materialized View: Example".
 
CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);

C:\Users\123\Desktop\每天工作明細\文件\oracle文件\oracle11g官方文件\server.112\e26088\statements_6003.htm

mv_log_purge_clause
下面各子項是相互排斥,僅能指定其中一個
此子句指定何時清除物化檢視日誌
Use this clause to specify the purge time for the materialized view log.
此選項為預設配置,即重新整理後馬上清除日誌
IMMEDIATE SYNCHRONOUS: the materialized view log is purged immediately after refresh. This is the default.
 
此選項即重新整理操作後,用一個獨立的oracle scheduler job進行清除日誌
IMMEDIATE ASYNCHRONOUS: the materialized view log is purged in a separate Oracle Scheduler job after the refresh operation.
 
start with ,next,repeat interval配置一個定時的清除任務,它任務獨立於物化檢視重新整理,在create or alter 物化檢視日誌期間進行
初始化.和create or alter materalized view語法的定時重新整理語法差不多.
START WITH, NEXT, and REPEAT INTERVAL set up a scheduled purge that is independent of the materialized view refresh and
is initiated during CREATE or ALTER MATERIALIZED VIEW LOG statement. This is very similar to scheduled refresh syntax
in a CREATE or ALTER MATERIALIZED VIEW statement:
start with指定何時開始清除日誌
The START WITH datetime expression specifies when the purge starts.
 
next計算得到下次清除日誌的時間
The NEXT datetime expression computes the next run time for the purge.
 
如指定了repeat interval,則下次清除時間為sysdate+interval_expr
If you specify REPEAT INTERVAL, then the next run time will be: SYSDATE + interval_expr.
會構建一個定時job,進行日誌清除,它會呼叫過程dbms_snapshot.purge_log清除物化檢視日誌. 可以在多次物化檢視重新整理中分攤這個清
除日誌的成本
A CREATE MATERIALIZED VIEW LOG statement with a scheduled purge creates an Oracle Scheduler job to perform. log purge.
The job calls the DBMS_SNAPSHOT.PURGE_LOG procedure to purge the materialized view logs. This process allows you to
amortize the purging costs over several materialized view refreshes.
 
mv_log_purge子句的操作限制:對於臨時表的物化檢視日誌是無效的
Restriction on mv_log_purge_clause This clause is not valid for materialized view logs on temporary tables.

---上述語法語義測試示例
SQL> create table t_mv(a int);
 
Table created
--說明建立物化檢視日誌,如新增選項pk,master table需構建pk
SQL> create materialized view log on t_mv with primary key;
 
create materialized view log on t_mv with primary key
 
ORA-12014: table 'T_MV' does not contain a primary key constraint
 
SQL> create materialized view log on t_mv with rowid;
 
Materialized view log created
-- 具備rowid的物化檢視日誌,不能實現增量重新整理
SQL> create materialized view mv_t refresh fast as select a from t_mv;
 
create materialized view mv_t refresh fast as select a from t_mv
 
ORA-12014: table 'T_MV' does not contain a primary key constraint
 
SQL> alter table t_mv add primary key(a);
 
Table altered
 
SQL> create materialized view log on t_mv with primary key;
 
Materialized view log created
 
SQL> create materialized view mv_t refresh fast as select a from t_mv;
 
Materialized view created
--說明增量重新整理也沒有實時同步master table變化到物化檢視
SQL> select * from t_mv;
 
                                      A
---------------------------------------
 
SQL> insert into t_mv values(1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from mv_t;
 
                                      A
---------------------------------------

SQL> create table t_mv(a int);
 
Table created
--with object id須master table為object table
SQL> create materialized view log on t_mv with object id;
 
create materialized view log on t_mv with object id
 
ORA-12086: table "SCOTT"."T_MV" is not an object table
--with sequence選項也要master table構建pk
SQL> create materialized view log on t_mv with sequence(a);
 
create materialized view log on t_mv with sequence(a)
 
ORA-12014: table 'T_MV' does not contain a primary key constraint
 
SQL> alter table t_mv add primary key(a);
 
Table altered
--with sequence(a)不能為過濾列,即pk列
SQL> create materialized view log on t_mv with sequence(a);
 
create materialized view log on t_mv with sequence(a)
 
ORA-12026: invalid filter column detected
--去除pk列,則建立成功
SQL> create materialized view log on t_mv with sequence;
 
Materialized view log created
--建立基於with sequence的增量重新整理物化檢視
SQL> create materialized view mv_t refresh fast as select a from t_mv;
 
Materialized view created
--如下說明with sequence可基於非主鍵列構建增量重新整理的物化檢視
--但自上測試,皆說明增量重新整理master table要構建pk
SQL> create table t_mv(a int,b int);
 
Table created
 
SQL> alter table t_mv add primary key(a);
 
Table altered
 
SQL> create materialized view log on t_mv with sequence(b);
 
Materialized view log created
 
SQL> create materialized view mv_t refresh fast as select a from t_mv;
 
Materialized view created