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