【ORACLE】物化檢視快速重新整理限制條件

快速重新整理的物化檢視建立比較麻煩，限制條件比較多，本文參考Oracle 11g 11.2版本官方文件，總結一般情況、含有聯接、含有聚合計算、UNION ALL等情況下的限制條件。

所有快速重新整理的物化檢視都必須滿足的條件

定義物化檢視的查詢語句限制如下：

• 1.物化檢視中不能含有非重複表示式的引用，例如SYSDATE和ROWNUM.
• 2.物化檢視中不能出現RAW或LONG RAW資料型別的引用。
• 3.查詢語句的SELECT部分語不能出現子查詢。
• 4.SELECT子句部分不能包含分析性質的函式，例如RANK。
• 5.不能含有MODEL子句。
• 6.子查詢中不能含有HAVING子句。
• 7.不能出現含有ANY,ALL或NOT EXISTS的巢狀子查詢。
• 8.不能含有[START WITH …] CONNECT BY子句。
• 9.不能包含不同站點的多個明細表。
• 10.ON COMMIT重新整理方式的物化檢視不能有遠端明細表的引用。
• 11.巢狀物化檢視必須含有表聯接或者聚合計算。
• 12.物化檢視包含聯接和GROUP BY子句的聚合計算時，不能select from 一個有索引的表。

只包含聯接的物化檢視快速重新整理限制條件

定義物化檢視的查詢語句只含有聯接且沒有聚合計算，實現快速重新整理除滿足通用條件外，還要滿足條件如下：

• 1.不能含有GROUP BY子句或聚合計算。
• 2.FROM中出現的每個基表的ROWID必須出現在SELECT子句中。
• 3.FROM中出現的每個基表都必須建立基於ROWID的物化檢視日誌(MATERIALIZED VIEW LOG)。
• 4.SELECT子句中不能含有object型別的列。

另外，以下情況將會影響快速重新整理的效率：

• 1.定義的查詢語句含有外聯接，如果這樣的話，請考慮重寫查詢語句改為內連線。
• 2.SELECT子句中含有多個表的列運算。

包含聚合的物化檢視快速重新整理限制

定義物化檢視的查詢語句含有聚合計算，實現快速重新整理除滿足通用條件外，還要滿足條件如下：

• 1.所有基表必須建立物化檢視日誌，建立日誌必須滿足：
  
  • 1.1 包含在物化檢視中引用的所有列，確保這些列沒有被加密。
  • 1.2 需要指定ROWID和INCLUDING NEW VAUES。
  • 1.3 如果基表會有插入/直接匯入，刪除，更新資料的混合操作，需要指定SEQUENCE子句。
• 2.只有SUM,COUNT,AVG,STDDEV,VARIANCE,MIN和MAX才支援快速重新整理。
• 3.必須有寫COUNT(*)。
• 4.聚合函式必須出現在表示式的最外層，也就是說像AVG(AVG(X))或AVG(x)+AVG(x)都是不允許的。
• 5.對每一個聚集計算如AVG(expr),相應的COUNT(expr)也必須要有，Oracle建議SUM(expr)也寫上。
• 6.如果含有VARIANCE(expr)或STDDEV(expr),COUNT(expr)和SUM(expr)都必須寫，Oracle建議SUM(expr*expr)也寫上。
• 7.查詢中定義的SELECT列不能是來自多個表的列複雜運算，可以考慮使用巢狀物化檢視作為變通方案。
• 8.GROUP BY中的列都要在SELECT裡出現。
• 9.如果物化檢視日誌中過濾列欄位型別為CHAR，那麼基表所在的資料庫和物化檢視所在的資料庫的字符集必須要一樣。
• 10.如果物化檢視有以下情況，增量重新整理只支援DML操作中的INSERT和直接裝載(direct loads)操作，這種物化檢視叫只能插入(insert-only)的物化檢視：
  
  • 10.1 物化檢視有MIN或者MAX聚合計算。
  • 10.2 物化檢視有SUM(expr)但是沒有COUNT(expr)。
  • 10.3 物化檢視沒有指定COUNT(*)。
• 11.含有MAX或MIN聚合計算的物化檢視如果沒有WHERE子句，是支援基表增刪改的增量重新整理的。
• 12.物化檢視的FROM子句中有普通檢視或者子查詢，如果普通檢視可以被完全合併是可以支援增量重新整理的。(PS.這一條不懂啥意思)
• 13.如果沒有外聯接，WHERE子句沒有限制。
• 14.有外聯接和聚合計算的物化檢視是支援增量重新整理的，只有外接表資料變更的時候。另外，唯一約束必須存在於表的內連線列上。如果有外聯接，所有連線必須用AND和=操作符。
• 15.對於含有CUBE，ROLLUP，GOUPING SETS的物化檢視，會有以下限制：
  
  • 15.1SELECT列表要包含GROUP BY所有欄位的GROUPING_ID或GROUPING。舉個例子，GROUP BY子句是GROUP BY CUBE(a, b)，那麼SELECT列表中應該包含GROUPING_ID(a, b)或GROUPING(a) , GROUPING(b)，這樣才會使物化檢視增量重新整理。
  • 15.2 GROUP BY不能導致多個groupings的情況，比如，GROUP BY a, ROLLUP(a, b)就不會增量重新整理因為這會導致多個groupings，(a), (a, b), (a)。

包含UNION ALL的物化檢視快速重新整理限制

含有UNION ALL的物化檢視REFRESH FAST需要滿足以下條件：

• 1.UNION ALL操作必須在查詢語句的頂層。UNION ALL不能被巢狀在一個子查詢裡面，這裡有一個例外：SELECT * FROM後面直接跟含有UNION ALL的子查詢。例如：
  CREATE VIEW view_with_unionall AS
  (SELECT c.rowid crid, c.cust_id, 2 umarker
  FROM customers c WHERE c.cust_last_name = ‘Smith’
  UNION ALL
  SELECT c.rowid crid, c.cust_id, 3 umarker
  FROM customers c WHERE c.cust_last_name = ‘Jones’);

  CREATE MATERIALIZED VIEW unionall_inside_view_mv
  REFRESH FAST ON DEMAND AS
  SELECT * FROM view_with_unionall;

• 2.UNION ALL的每一部分查詢必須滿足聚合物化檢視貨連線物化檢視的增量重新整理條件。相應的物化檢視日誌也要建上。

• 3.UNION ALL的每一部分查詢必須包含一列標記列，這個標識列必須是數值或者字元型的固定數值，而且這一列在每一部分的查詢中SELECT列順序相同。
• 4.一些特性如外聯接，只能插入(insert-only)的物化檢視查詢和遠端表都是不支援UNION ALL物化檢視的。然而，那些被用於複製的不包含聯接和聚集計算的物化檢視，是可以增量重新整理的。
• 5.建立包含UNION ALL的增量重新整理物化檢視，版本需滿足9.2.0或更高才能滿足相容。

參考：
https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007013

更多：
http://c-xuan.com/2017/07/13/20170713001