ZT 定位導致物化檢視無法快速重新整理的原因

物化檢視的快速重新整理採用了增量的機制，在重新整理時，只針對基表上發生變化的資料進行重新整理。因此快速重新整理是物化檢視重新整理方式的首選。

但是快速重新整理具有較多的約束，而且對於採用ON COMMIT模式進行快速重新整理的物化檢視更是如此。對於包含聚集和包含連線的物化檢視的快速重新整理機制並不相同，而且對於多層巢狀的物化檢視的快速重新整理更是有額外的要求。

如此多的限制一般很難記全，當建立物化檢視失敗時，Oracle給出的錯誤資訊又過於簡單，有時無法使你準確定位到問題的原因。

Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW過程可以幫助你快速定位問題的原因。下面透過一個例子來說明，如果透過這個過程來解決問題。

建立一個快速重新整理的巢狀物化檢視：

SQL> CREATE TABLE B (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已建立。

SQL> CREATE TABLE C (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已建立。

SQL> CREATE TABLE A (ID NUMBER, BID NUMBER, CID NUMBER, NUM NUMBER,
2 CONSTRAINT FK_A_B_BID FOREIGN KEY (BID) REFERENCES B(ID),
3 CONSTRAINT FK_A_C_BID FOREIGN KEY (CID) REFERENCES C(ID));

表已建立。

SQL> INSERT INTO B SELECT ROWNUM, 'B'||ROWNUM FROM USER_TABLES WHERE ROWNUM <= 6;

已建立6行。

SQL> INSERT INTO C SELECT ROWNUM, 'C'||ROWNUM FROM USER_TABLES WHERE ROWNUM <= 4;

已建立4行。

SQL> INSERT INTO A SELECT ROWNUM, TRUNC((ROWNUM - 1)/2) + 1, TRUNC((ROWNUM - 1)/3) + 1, ROWNUM
2 FROM USER_TABLES
3 WHERE ROWNUM <= 12;

已建立12行。

SQL> COMMIT;

提交完成。

上面建立好基表，下面建立第一層物化檢視。

SQL> CREATE MATERIALIZED VIEW LOG ON A WITH ROWID;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW LOG ON B WITH ROWID;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW LOG ON C WITH ROWID;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
2 SELECT C.ID CID, C.NAME CNAME, B.ID BID, B.NAME BNAME, A.NUM,
3 A.ROWID AROWID, B.ROWID BROWID, C.ROWID CROWID
4 FROM A, B, C WHERE A.BID = B.ID AND A.CID = C.ID;

實體化檢視已建立。

第一次物化檢視已經建立成功，下面建立巢狀物化檢視：

SQL> CREATE MATERIALIZED VIEW LOG ON MV_ABC WITH ROWID (BNAME, CNAME, NUM) INCLUDING NEW VALUES;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
2 SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC
3 GROUP BY CNAME, BNAME;
SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC
*
ERROR 位於第 2 行:
ORA-12053: 這不是一個有效的巢狀實體化檢視

錯誤出現了，不過錯誤的描述包含的資訊量並不大。我們看看Oracle的文件上是如何描述這個錯誤的。

ORA-12053 this is not a valid nested materialized view

Cause: The list of objects in the FROM clause of the definition of this materialized view had some dependencies upon each other.

Action: Refer to the documentation to see which types of nesting are valid.

文件上的描述也是十分籠統的，並沒有指出具體問題所在。

接下來，我們透過使用DBMS_MVIEW.EXPLAIN_MVIEW過程來定位錯誤。

使用EXPLAIN_MVIEW過程首先要建立MV_CAPABILITIES_TABLE表，建表的腳步是$ORACLE_HOME/rdbms/admin/utlxmv.sql。（EXPLAIN_MVIEW過程是兩個過程的過載，一個輸出到MV_CAPABILITIES_TABLE表，另一個以PL/SQL的VARRAY格式輸出，為了簡單起見，我們建立MV_CAPABILITIES_TABLE表）。

SQL> @?rdbmsadminutlxmv.sql

表已建立。

下面簡單研究一下EXPLAIN_MVIEW過程。

DBMS_MVIEW.EXPLAIN_MVIEW(mv IN VARCHAR2, Statement_id IN VARCHAR2:= NULL);

該過程可以輸入已經存在的物化檢視名稱（或USER_NAME.MV_NAME），也可輸入建立物化檢視的查詢語句。另外一個引數STATEMENT_ID輸入一個語句ID，為了標識出表中對應的記錄。

SQL> BEGIN
2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC
3 GROUP BY CNAME, BNAME', 'MV_MV_ABC');
4 END;
5 /

PL/SQL 過程已成功完成。

SQL> SELECT CAPABILITY_NAME, RELATED_TEXT, MSGTXT FROM MV_CAPABILITIES_TABLE
2 WHERE STATEMENT_ID = 'MV_MV_ABC' AND POSSIBLE = 'N' AND CAPABILITY_NAME NOT LIKE '%PCT%';

CAPABILITY_NAME RELATED_TEXT MSGTXT
------------------------------ --------------- --------------------------------------------------
REFRESH_FAST_AFTER_ONETAB_DML SUM_NUM 使用 SUM(expr) 時, 未提供 COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML YANGTK.MV_ABC mv 日誌沒有序列號
REFRESH_FAST_AFTER_ANY_DML 檢視禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因

根據上面的資訊，已經可以確定問題的原因了，對於聚集物化檢視，使用了SUM(COLUMN)，但是沒有包括COUNT(COLUMN)。

修改物化檢視，重新建立：

SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
2 SELECT CNAME, BNAME, COUNT(*) COUNT, COUNT(NUM) NUM_COUNT, SUM(NUM) SUM_NUM FROM MV_ABC
3 GROUP BY CNAME, BNAME;

實體化檢視已建立。