`

定位导致物化视图无法快速刷新的原因

 
阅读更多
物化视图的快速刷新采用了增量的机制,在刷新时,只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。

但是快速刷新具有较多的约束,而且对于采用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;

实体化视图已创建。
分享到:
评论

相关推荐

    物化视图的快速刷新

    物化视图的快速刷新 物化视图是 Oracle 中的一种性能优化技术,它可以将复杂的查询结果存储在一个物化视图中,以便快速地检索数据。物化视图有三种刷新方式:COMPLETE、FAST 和 FORCE。 COMPLETE 刷新方式会删除表...

    Oracle怎么根据物化视图日志快速刷新物化视图

    ### Oracle如何根据物化视图日志快速刷新物化视图 #### 一、物化视图及其日志介绍 在Oracle数据库中,物化视图(Materialized View)是一种特殊的对象,它存储了预计算查询的结果,从而可以提高查询性能。物化视图...

    PostgreSQL物化视图的刷新机制.pptx

    PostgreSQL 物化视图的刷新机制 PostgreSQL 物化视图是一种特殊的视图,它可以存储数据,如同一个表一样,但它的数据可以根据原表的变化而变化。物化视图可以分为四种类型:快照物化视图、积极物化视图、消极物化...

    Oracle物化视图增量刷新的应用研究.pdf

    快速刷新采用增量刷新机制,基于日志挖掘原理,所有的刷新动作都是通过基表的物化视图日志来实现。快速刷新可分为自动刷新及手动刷新。自动刷新可以事先定义刷新时间、刷新间隔等参数,系统会根据要求定时刷新。手动...

    Oracle物化视图应用详解

    - `REFRESH` 选项定义了刷新策略,如 `FORCE` 在每次查询时强制刷新,`FAST` 利用物化视图日志进行快速刷新,`COMPLETE` 是完全刷新,`NEVER` 表示不自动刷新。 - `ON COMMIT` 或 `ON DEMAND` 定义了刷新时机,`ON ...

    Oracle数据库中物化视图的原理剖析

    输出的脚本可能包括创建物化视图日志(Materialized View Log)的命令,物化视图日志是快速刷新机制的关键组成部分,它们记录源表的变化,以便物化视图能够追踪和更新。 总之,Oracle数据库中的物化视图是一种强大...

    ORACLE9I物化视图

    为了应对这一挑战,Oracle 9i 引入了物化视图的概念,这是一种用于快速访问和报告数据的有效手段。 #### 引言 物化视图最早在 Oracle 8i 中引入,并且作为 Summary Management 组件的一部分。许多组织可能已经在...

    Oracle物化视图创建和使用

    每种物化视图都有其快速刷新的限制条件,但其他方面则区别不大。 在学习 Oracle 物化视图时,需要了解物化视图的概念、创建和删除物化视图、设置自动刷新时间和日志等知识点。同时,也需要了解物化视图的优点和缺点...

    oracle物化视图_循序渐进学习笔记

    Oracle物化视图是一种在数据库中预先计算并存储视图查询结果的数据对象,它与普通的视图不同,普通视图在查询时动态地基于基表数据生成结果,而物化视图则拥有自己的物理存储,提供了对数据的快速访问。在本篇循序渐...

    物化视图普通视图区别,很不错的资料

    物化视图对于大数据分析、报表生成和OLAP(在线分析处理)场景特别有用,因为它允许用户快速获取预计算的结果,而无需每次都执行复杂的查询。然而,物化视图也会占用额外的存储空间,并且需要维护与基表数据的一致性...

    SQL 优化之 oracle物化视图

    2. 主键物化视图允许识别物化视图的主表,不影响物化视图的增量刷新可用性。 Rowid 子句: 1. 生成 Rowid 物化视图,基于 Rowid 的物化视图。 2. Rowid 物化视图只有一个单一的主表,不能包括 Distinct 或者聚合...

    物化视图的两种实现

    总结起来,物化视图的两种实现方式各有优势,直接设计简单明了,适合快速初始化和即时刷新;定时任务触发存储过程则更适合需要定期更新且对性能有一定要求的场景。选择哪种实现方式,应根据具体的应用环境和业务需求...

    物化视图创建脚本物化视图创建脚本

    物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建

    oracle 物化视图详解(内含例子)

    1. **ONDEMAND**:默认情况下,物化视图采用的是ONDEMAND模式,即只有当用户显式地请求刷新时,Oracle才会更新物化视图中的数据。这是一种最简单的创建方式,适用于数据变化不频繁或者对数据新鲜度要求不高的场景。 ...

    Oracle物化视图使用[文].pdf

    ON DEMAND模式意味着物化视图只在需要时(如用户手动触发或定时任务)进行刷新,以保持与基表数据的一致性。而ON COMMIT模式则更积极,一旦基表中的事务提交,物化视图就会立即刷新,确保数据即时更新。 创建一个ON...

    创建物化视图ORA-12014错误解决方法

    4. **其他配置问题**:如表空间设置、物化视图的刷新策略等也可能导致此错误。 #### 解决方案 针对上述原因,我们可以采取以下措施来解决ORA-12014错误: 1. **检查基表结构**: - 确认基表是否定义了主键或唯一...

    物化视图,数据仓库,Oracle

    - **刷新物化视图**:定期或按需刷新物化视图以保持数据的最新状态。 - **查询物化视图**:通过简单的SELECT语句直接查询物化视图。 - **维护物化视图**:包括监控物化视图的状态、执行维护任务等。 #### 四、物化...

    MySQL中实现物化视图

    真正值得一看的mysql知识。MySQL中实现物化视图(中文版)翻译于外文。

    ORACLE 物化视图 详解

    主键物化视图允许在不影响增量刷新的前提下,通过主键快速定位主表中的记录。 #### 六、小结 通过上述介绍,我们可以看出物化视图是Oracle数据库中一种非常实用的工具,它能够显著提高复杂查询的性能,并简化数据...

    表分区及物化视图

    orace表分区及物化视图 进一步了解oracle表分区技术及物化视图技术应用

Global site tag (gtag.js) - Google Analytics