MATERIALIZED VIEW

xteitxu

oracle版本 11.1.0.6    由于开发对mv的基表的字段做了modify，（mv的数据也是从view里获取）。开发做完后，mv的状态就成stale了。我重新refresh物化视图后，状态为fresh。但下一个时间刷新物化视图后，状态又成needs_compile,对其compile后，重新刷新，状态又成stale。不解原因出在那里。求解

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROM USER_MVIEWS;
MVIEW_NAME                                                   LAST_REFRESH_DATE   STALENESS
------------------------------------------------------------ ------------------- ----------------------
CLAIMS_CSHOS_PREPAID_VIEW                                    2012-04-10 16:38:21 NEEDS_COMPILE

syhnd

After executing DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), the USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.


    In fact, this is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

Maclean Liu(刘相兵

正常现象  9.0.1.0 to 11.1.0.6 中均可能发生 ，STALENESS=NEEDS_COMPILE不影响 MV 正常查询


ODM FINDING:

After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID'

Applies to:
Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.1.0.6 - Release: 9.0.1 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 27-oct-2010***

Local Materialized Views (MVs). i.e. the MV and its master table(s) exist in a single database.
Symptoms

After executing DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), the USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.

Example :
SQL> select object_name, object_type, status from user_objects
2 where object_type = 'MATERIALIZED VIEW';
Output :
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------ -------
EMP_MV_PK MATERIALIZED VIEW VALID


SQL> select mview_name, compile_state from user_mviews;
Output :
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
EMP_MV_PK VALID


SQL> update emp set empno = empno where empno = 605;
SQL> commit;


SQL> select object_name, object_type, status from user_objects
2 where object_type = 'MATERIALIZED VIEW';
Output :
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------ -------
EMP_MV_PK MATERIALIZED VIEW INVALID


SQL> select mview_name, compile_state from user_mviews;
Output :
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
EMP_MV_PK NEEDS_COMPILE

Changes
DML was executed and committed on master table of the MV since the MV was last refreshed.
Cause
This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.
Solution
Though the status is INVALID, the MV can still be queried.  However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :
SQL> select object_name, object_type, status from user_objects
2 where object_type = 'MATERIALIZED VIEW';
Output :
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------ -------
EMP_MV_PK MATERIALIZED VIEW INVALID


SQL> select mview_name, compile_state from user_mviews;
Output :
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
EMP_MV_PK NEEDS_COMPILE


SQL> select count(*) from emp_mv_pk;
Output :
COUNT(*)
----------
2686976



- On next refresh, the status becomes VALID.

Example :
SQL> execute dbms_mview.refresh('emp_mv_pk');
SQL> select object_name, object_type, status from user_objects
2 where object_type = 'MATERIALIZED VIEW';
Output :
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------ -------
EMP_MV_PK MATERIALIZED VIEW VALID


SQL> select mview_name, compile_state from user_mviews;
Output :
MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
EMP_MV_PK VALID



"REFRESH ON COMMIT" MVs are exception to this behavior because they are refreshed at the
same time of DML.

xteitxu

非常感谢各位。确实STALENESS=NEEDS_COMPILE不影响 MV 正常查询。业务不受影响。