oracle物化视图日志系列(三)
--报错原因未添加主键列或filtered column到物化视图日志的with rowid之中
SQL> create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a;
create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_MV"
SQL> drop materialized view log on t_mv;
Materialized view log dropped
SQL> create materialized view log on t_mv with rowid(a) including new values;
Materialized view log created
SQL> create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a;
Materialized view created
--报错因为未添加with rowid选项到物化视图日志
SQL> create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a;
create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_MV"
--如下错错,因为单表聚集物化视图的物化视图日志必须添加including new values
SQL> create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a;
create materialized view mv_t refresh fast as select a,count(a) from t_mv group by a
ORA-32401: materialized view log on "SCOTT"."T_MV" does not have new values
---下面字典可以查询物化视图日志各个参数的配置信息
SQL> desc user_mview_logs;
Name Type Nullable Default Comments
------------------ ------------- -------- ------- -----------------------------------------------------------------------------------
LOG_OWNER VARCHAR2(30) Y Owner of the materialized view log
MASTER VARCHAR2(30) Y Name of the master table which changes are logged
LOG_TABLE VARCHAR2(30) Y Log table; with rowids and timestamps of rows which changed in the
master
LOG_TRIGGER VARCHAR2(30) Y Trigger on master table; fills the materialized view log
ROWIDS VARCHAR2(3) Y If YES, the materialized view log records rowid information
PRIMARY_KEY VARCHAR2(3) Y If YES, the materialized view log records primary key information
OBJECT_ID VARCHAR2(3) Y If YES, the materialized view log records object id information
FILTER_COLUMNS VARCHAR2(3) Y If YES, the materialized view log records filter column information
SEQUENCE VARCHAR2(3) Y If YES, the materialized view log records sequence information
INCLUDE_NEW_VALUES VARCHAR2(3) Y If YES, the materialized view log records old and new values (else only old values)
PURGE_ASYNCHRONOUS VARCHAR2(3) Y If YES, the materialized view log is purged asynchronously
PURGE_DEFERRED VARCHAR2(3) Y If YES, the materialized view log is purged in a deferred manner
PURGE_START DATE Y For deferred purge, the purge start date
PURGE_INTERVAL VARCHAR2(200) Y For deferred purge, the purge interval
LAST_PURGE_DATE DATE Y Date of the last purge
LAST_PURGE_STATUS NUMBER Y Status of the last purge: error code or 0 for success
NUM_ROWS_PURGED NUMBER Y Number of rows purged in the last purge
COMMIT_SCN_BASED VARCHAR2(3) Y If YES, the materialized view log is commit SCN-based
--指定开始清除日志的时间
SQL> create materialized view log on t_mv with rowid,primary key including new values purge start with sysdate;
Materialized view log created
--同时指定下次清除日志时间
SQL> create materialized view log on t_mv with rowid,primary key including new values purge start with sysdate next sysdate+3;
Materialized view log created
--指定清除间隔
SQL> create materialized view log on t_mv with rowid,primary key including new values purge start with sysdate repeat interval 3
2 ;
Materialized view log created
--测试基于时间戳和基于scn,默认是基于timestamp,此效能低,显式可创建scn
SQL> create materialized view log on t_mv with commit scn;
Materialized view log created
--查询基于scn or timestamp
SQL> select COMMIT_SCN_BASED from user_mview_logs;
COMMIT_SCN_BASED
----------------
YES
SQL> drop materialized view log on t_mv;
Materialized view log dropped
SQL> create materialized view log on t_mv;
Materialized view log created
SQL> select COMMIT_SCN_BASED from user_mview_logs;
COMMIT_SCN_BASED
----------------
NO
--测试filter column,说明master table的pk列不能用于物化视图日志的
filter column
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
SQL> desc t_mv;
Name Type Nullable Default Comments
---- ------- -------- ------- --------
A INTEGER
SQL> alter table t_mv add b int;
Table altered
SQL> create materialized view log on t_mv with sequence(b);
Materialized view log created
SQL> select * from user_mview_logs;
LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER ROWIDS PRIMARY_KEY OBJECT_ID FILTER_COLUMNS SEQUENCE INCLUDE_NEW_VALUES PURGE_ASYNCHRONOUS PURGE_DEFERRED PURGE_START PURGE_INTERVAL LAST_PURGE_DATE LAST_PURGE_STATUS NUM_ROWS_PURGED COMMIT_SCN_BASED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------ ----------- --------- -------------- -------- ------------------ ------------------ -------------- ----------- -------------------------------------------------------------------------------- --------------- ----------------- --------------- ----------------
SCOTT T_MV MLOG$_T_MV NO YES NO YES YES NO NO NO NO
SQL> select FILTER_COLUMNS from user_mview_logs;
FILTER_COLUMNS
--------------
YES
--如下说明可同时指定多个with rowid,primary key,但不能同时指定多个filter column
SQL> create materialized view log on t_mv with rowid(b);
Materialized view log created
SQL> drop materialized view log on t_mv;
Materialized view log dropped
SQL> create materialized view log on t_mv with rowid(b),sequence;
Materialized view log created
SQL> drop materialized view log on t_mv;
Materialized view log dropped
SQL> create materialized view log on t_mv with primary key(b),rowid(b);
create materialized view log on t_mv with primary key(b),rowid(b)
ORA-00922: missing or invalid option