包含複雜查詢的快速重新整理的物化檢視

物化檢視的快速重新整理條件在以前已經介紹過了，有的時候還有一些朋友會問我關於物化檢視快速重新整理的一些問題，其實有些時候一些複雜的物化檢視仍然可以透過多層巢狀來實現快速重新整理的。

物化檢視的快速重新整理（一）：http://yangtingkun.itpub.net/post/468/14245

物化檢視的快速重新整理（二）：http://yangtingkun.itpub.net/post/468/16456

物化檢視的快速重新整理（三）：http://yangtingkun.itpub.net/post/468/16496

 

 

一個經過簡化的例子：

CREATE TABLE t1(c1 NUMBER);
CREATE TABLE t2(c1 NUMBER);
CREATE TABLE t3(c1 NUMBER,c2 NUMBER);

-- 建立mv的查詢
SELECT t2.c1
      ,t4.c1
      ,MAX(t3.c1)
FROM (SELECT MAX(t1.c1) c1
      FROM t1) t4
    ,t2
    ,t3
WHERE t3.c1 > t4.c1
      AND t2.c1 = t3.c2
GROUP BY t2.c1
        ,t4.c1

對於這種包含兩層巢狀的查詢，直接建立成快速重新整理方式的物化檢視顯然是不可能的，但是可以利用中間物化檢視進行過渡，而且對於10.2中，物化檢視快速重新整理的限制條件進一步放寬，使得包含MAX/MIN聚集函式的物化檢視快速重新整理的條件得以放寬：

SQL> CREATE TABLE T1(C1 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 NUMBER);

Table created.

SQL> CREATE TABLE T3(C1 NUMBER,C2 NUMBER);

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T1
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON T2
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON T3
  2  WITH ROWID, SEQUENCE (C1, C2)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T4
  2  REFRESH FAST AS
  3  SELECT COUNT(*) CN, COUNT(C1), MAX(T1.C1) C1
  4  FROM T1;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T4
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW  MV_T123
  2  REFRESH FAST AS
  3  SELECT T2.C1 T2_C1, MV_T4.C1 T4_C1, COUNT(*) CNT, COUNT(T3.C1), MAX(T3.C1)
  4  FROM MV_T4, T2, T3
  5  WHERE T3.C1 > MV_T4.C1
  6  AND T2.C1 = T3.C2
  7  GROUP BY T2.C1, MV_T4.C1;

Materialized view created.

利用中間物化檢視，這種多層的快速重新整理物化檢視可以很容易的建立。

不過需要注意的是，包含MAX/MIN的物化檢視，仍然需要INSERT ONLY物化檢視，對於包含UPDATE和DELETE操作的情況是不使用的。

SQL> @?/rdbms/admin/utlxmv

Table created.

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_T123')

PL/SQL procedure successfully completed.

SQL> SELECT CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT
  2  FROM MV_CAPABILITIES_TABLE
  3  WHERE CAPABILITY_NAME NOT LIKE '%PCT%'
  4  AND CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME               P RELATED_TEXT MSGTXT
----------------------------- - ------------ ----------------------------------------------------------
REFRESH_COMPLETE              Y
REFRESH_FAST                  Y
REFRESH_FAST_AFTER_INSERT     Y
REFRESH_FAST_AFTER_ONETAB_DML N MAX(T3.C1)   mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ANY_DML    N              see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

對於MAX/MIN的INSERT ONLY特性，沒有什麼太好的辦法，這裡介紹了一個例子，可以在一定程度上解決這個問題：

改造包含MAX或MIN的物化檢視：http://yangtingkun.itpub.net/post/468/492826