oracle技术之Oracle 物化视图（二）

89ou

　　二. 物化视图实例
　　物化视图的快速刷新采用了增量的机制，在刷新时，只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。
　　但是快速刷新具有较多的约束，而且对于采用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  INSERT INTO C SELECT ROWNUM, 'C'||ROWNUM FROM USER_TABLES WHERE ROWNUM  INSERT INTO A SELECT ROWNUM, TRUNC((ROWNUM - 1)/2) + 1, TRUNC((ROWNUM - 1)/3) + 1, ROWNUM
　　2  FROM USER_TABLES
　　3  WHERE ROWNUM  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,>　　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;
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html