物化视图失效的几种情况及测试 |
您所在的位置:网站首页 › 物化视图失效原因 › 物化视图失效的几种情况及测试 |
说明:物化视图(Materialized Views)是查询的结果集,所有原表和视图的变更都将导致物化视图的失效,最近就发现了一个物化视图经常失效的问题,以下是整理的文档。
一、物化视图状态查询:ORACLE提供了一个视图用于查询物化视图的状态USER_MVIEWS,其中列STALENESS,用于显示当前物化视图的状态 Relationship between the contents of the materialized view and the contents of the materialized view's masters: FRESH - Materialized view is a read-consistent view of the current state of its masters(最新状态:当前物化视图的内容出于最新的状态) STALE - Materialized view is out of date because one or more of its masters has changed. If the materialized view was FRESH before it became STALE, then it is a read-consistent view of a former state of its masters.(陈旧状态:物化视图引用的主表已经更新,但是物化视图没有刷新,所以内容相对主表来说是旧的) NEEDS_COMPILE - Some object upon which the materialized view depends has changed. An ALTER MATERIALIZED VIEW...COMPILE statement is required to validate this materialized view and compute the staleness of the contents.(需要编译:物化视图引用的主表比如视图,进行了重建后相应的物化视图就需要编译,当处于这种状态的时候dba_objects视图显示的STATUS为INVALID)需要运行语句:ALTER MATERIALIZED VIEW MV_NAME COMPILE;进行重新编译; UNUSABLE - Materialized view is not a read-consistent view of its masters from any point in time(物化视图引用的主表状态不确定) UNKNOWN - Oracle Database does not know whether the materialized view is in a read-consistent view of its masters from any point in time (this is the case for materialized views created on prebuilt tables)(未知:通过prebuilt创建的表) UNDEFINED - Materialized view has remote masters. The concept of staleness is not defined for such materialized views.(物化视图引用的表来自其他的数据库,一般通过dblink链接过来的)
二、实验测试: 2.1 创建物化视图 CREATE MATERIALIZED VIEW MV_TEST (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) TABLESPACE USERS PCTUSED 0 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE USING INDEX TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS /* Formatted on 2014/12/30 16:36:55 (QP5 v5.215.12089.38647) */ SELECT "EMP"."EMPNO" "EMPNO", "EMP"."ENAME" "ENAME", "EMP"."JOB" "JOB", "EMP"."MGR" "MGR", "EMP"."HIREDATE" "HIREDATE", "EMP"."SAL" "SAL", "EMP"."COMM" "COMM", "EMP"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "EMP" WHERE "EMP"."DEPTNO" = 20;
COMMENT ON MATERIALIZED VIEW MV_TEST IS 'snapshot table for snapshot SCOTT.MV_TEST';
CREATE UNIQUE INDEX PK_EMP1 ON MV_TEST (EMPNO) LOGGING TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) NOPARALLEL;
2.2 查询当前物化视图的状态 SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST';
MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST FRESH
2.3 查询数据库物化视图的状态 column OBJECT_NAME format a20; column STATUS format a20; OBJECT_TYPE column OBJECT_TYPE format a20; SQL> select OBJECT_NAME,STATUS,OBJECT_TYPE from dba_objects where OBJECT_NAME='MV_TEST' ;
OBJECT_NAME STATUS OBJECT_TYPE -------------------- -------------------- -------------------- MV_TEST VALID TABLE MV_TEST VALID MATERIALIZED VIEW
2.4 修改源表的数据 SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST'; MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST NEEDS_COMPILE 显示状态需要编译
SQL> ALTER MATERIALIZED VIEW SCOTT.MV_TEST COMPILE; 进行相应的编译
SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST'; MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST STALE 编译完成后,状态变成STALE
2.5 修改源表的表结构测试 SQL> ALTER TABLE SCOTT.EMP RENAME COLUMN COMM TO COMMS; //修改源表的结构
SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST';//查看物化视图的状态 MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST NEEDS_COMPILE
SQL> ALTER MATERIALIZED VIEW SCOTT.MV_TEST COMPILE; //重新编译
SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST'; //重新编译状态没变;
MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST NEEDS_COMPILE
SQL> select OBJECT_NAME,STATUS,OBJECT_TYPE from dba_objects where OBJECT_NAME='MV_TEST' ;
OBJECT_NAME STATUS OBJECT_TYPE -------------------- -------------------- -------------------- MV_TEST VALID TABLE MV_TEST INVALID MATERIALIZED VIEW 显示物化视图的状态INVALID
2.5 修改源表的结构跟物化视图一致 SQL> ALTER TABLE SCOTT.EMP RENAME COLUMN COMMS TO COMM; //修改源表的结构 SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST'; //需要编译 MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST NEEDS_COMPILE
SQL> ALTER MATERIALIZED VIEW SCOTT.MV_TEST COMPILE; //进行重新编译
Materialized view altered.
SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST'; //状态变成STALE MVIEW_NAME STALENESS ------------------------------ ------------------- MV_TEST STALE
SQL> select OBJECT_NAME,STATUS,OBJECT_TYPE from dba_objects where OBJECT_NAME='MV_TEST' ; //状态变成VALID
OBJECT_NAME STATUS OBJECT_TYPE -------------------- -------------------- -------------------- MV_TEST VALID TABLE MV_TEST VALID MATERIALIZED VIEW
总结:当物化视图的源表重新编译了,如果重建后的表结构没有发现变化,那么运行脚本ALTER MATERIALIZED VIEW MV_NAME COMPILE后物化视图的状态就会刷新成有效的; 但是如果表的结构发生了变化,那么需要重新修改物化视图的脚本,相应的物化视图才能有效,dba_objects显示出来的状态才是VALID的状态;
****************************************************************************************************** 本文作者:JOHN QQ:1916066696 (请备注数据库) ORACLE技术博客:ORACLE 猎人笔记 http://blog.itpub.net/12679300/ ******************************************************************************************************************** |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |