Oracle物化視圖失效的幾種情況及測試
說明:物化視圖(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的狀態;
物化視圖定義不當引發Oracle效能問題
Oracle物化視圖測試
Oracle【定期重新整理】的【基於串連】的物化視圖
Oracle物化視圖建立報ORA-00942錯誤解決
Oracle 預存程序重新整理物化視圖
Oracle物化視圖的使用