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

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