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

oracle物化檢視日誌系列(一)
1,基於物化檢視日誌,實現增量重新整理,如無,則重新執行查詢模組,進行全量重新整理
2,如要實現增量重新整理,必須為查詢模組所有引用的表構建物化檢視日誌
3,增量重新整理比全量重新整理花費時間要少得多

有2種型別的物化檢視日誌:基於時間戳和基於提交時產生的scn.
如採用前者,使用時間戳並且
需要進行一些配置操作.而後者使用提交時產生的SCN,不用配置操作,
因此提升了重新整理的速度.如指定commit scn子句會建立基於scn的物化檢視日誌,
否則建立基於時間戳的物化檢視日誌

只有新建立的物化檢視日誌可以使用commit scn,已建立的物化檢視日誌不能變更
新增commit scn,除非刪除重建

語法:
CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
  [ physical_attributes_clause
  | TABLESPACE tablespace
  | logging_clause
  | { CACHE | NOCACHE }
  ]...
  [ parallel_clause ]
  [ table_partitioning_clauses ]
  [ WITH [ { OBJECT ID
         | PRIMARY KEY
         | ROWID
         | SEQUENCE
         | COMMIT SCN
         }
           [ { , OBJECT ID
             | , PRIMARY KEY
             | , ROWID
             | , SEQUENCE
             | , COMMIT SCN
             }
           ]... ]
    (column [, column ]...)
    [ new_values_clause ]
  ] [ mv_log_purge_clause ]
;

--上述各子句語法構成
new_values_clause::=
{ INCLUDING | EXCLUDING } NEW VALUES

mv_log_purge_clause::=

PURGE { IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ]
      | START WITH datetime_expr [ NEXT datetime_expr
                                 | REPEAT INTERVAL interval_expr
                                 ]
      | [ START WITH datetime_expr ] { NEXT datetime_expr
                                     | REPEAT INTERVAL interval_expr
                                     }
      }

WITH Clause
1,此子句標示master table row record變化時到底到底記錄pk,object id,sequence,rowid或其混合體.
  也可用此子句新增序列到物化檢視日誌,為其記錄提供額外的排序資訊
2,也控制是否記錄物化檢視中的過濾列,即在物化檢視子查詢中引用的非主鍵列,或子查詢多表關聯的
  連線列(也是非主鍵列)
3,預設為主鍵列
4,object id僅適用於物件表;表明記錄系統生成或使用者定義的物件表
5,rowid表示記錄變化的rowid儲存到物化檢視日誌
6,sequence可以提供增量重新整理,

COMMIT SCN
如未指定commit scn子句,預設啟用基於時間戳,需要執行一些配置操作.而commit scn不用這些操作提升
重新整理速度.

可以為基於scn的物化檢視日誌的master table建立下列型別的本地物化檢視(包括on commit和on demand)

聚集物化檢視,包含基於單表的聚集物化檢視
Materialized aggregate views, including materialized aggregate views on a single table
 
聯結物化檢視
Materialized join views
 
基於PK和基於ROWID的單表物化檢視
Primary-key-based and rowid-based single table materialized views
 
透過union all聯結上述型別物化檢視的物化檢視
UNION ALL materialized views, where each UNION ALL branch is one of the above materialized view types
 
記住:不能為建立了基於scn物化檢視日誌的master table建立遠端物化檢視
You cannot create remote materialized views on master tables with commit SCN-based materialized view logs.

commit scn一些操作限制
Restrictions on COMMIT SCN The following restrictions apply to COMMIT SCN:
不支援表中包含多個lob列否則報ora-32421
Use of COMMIT SCN on a table with one or more LOB columns is not supported and causes ORA-32421.
 
如建立不同型別(即基於scn和基於時間戳)物化檢視日誌型別的物化檢視不能支援會報ora-32421
Creating a materialized view on master tables with different types of materialized view logs
(that is, a master table with timestamp-based materialized view logs and a master table with
commit SCN-based materialized view logs) is not supported and causes ORA-32414.
 
column表明如記錄發生變化時,記錄這些記錄哪些列到物化檢視日誌中,一般是filter columns或join columns
column Specify the columns whose values you want to be recorded in the materialized view log
for all rows that are changed. Typically these columns are filter columns and join columns.