物化视图失效的几种情况及测试

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链接过来的）

SQL> SELECT mview_name,staleness FROM DBA_MVIEWS WHERE MVIEW_NAME='MV_TEST';

MVIEW_NAME               STALENESS

------------------------------ -------------------

MV_TEST                FRESH

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

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

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

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