Oracle物化視圖失效的幾種情況及測試

來源:互聯網
上載者:User

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物化視圖的使用

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.