oracle如何确定物化视图完成,Oracle 物化视图使用教程(2)

实体化视图日志已创建。

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;

删除物化视图日志

--删除日志：

DROP materialized view log on A;

--删除物化视图

DROP materialized view MV_ABC;