ORACLE中的物化檢視（OCM複習總結）

1、  基本概念

檢視是一個虛擬表，基於它建立時指定的查詢語句返回結果集。每次訪問它都會導致這個查詢語句被執行一次。為了避免每次訪問都執行這個查詢，可以將這個查詢的結果集儲存到一個物化檢視。也就是說，物化檢視只是對已經儲存於別處的資料的轉換和複製。

        執行建立物化檢視的語句實際上會建立一個物化檢視和一個容器表，容器表是一個普通的表，它與物化檢視擁有同樣的名稱，並且這個表可以像任何其他表一樣被查詢。執行計劃中的MAT_VIEW_ACCESS_FULL表明使用了物化檢視，直接使用容器表所對應的語句是一種方式，而另外一種方式就是查詢重寫。

        查詢重寫也就是當查詢最佳化器收到一條待最佳化的查詢，可以選擇物化檢視來對它進行重寫，只要這個物化檢視包含查詢的全部和部分資料。可以使用HINT REWRITE和NO_REWRITE來影響最佳化器的決定。

        要執行查詢重寫必須設定引數query_rewirte_enabled設定為TRUE，其次還必須對這個物化檢視啟用查詢重寫。

         Alter materialized view ** enable query rewrite

2、  引數

        語法：

        Create materialized view **

{TABLESPACE ts_name}

        Refresh fast/complete/force with rowid/primary key on commit/demand

        {on demand start with syadate next sysdate+1}

        { NEVER REFRESH }

        Enable query rewirte

        Build immediate/deferred

        {Using on index}

        As

        *************;

       create materialized view log on t_rowid with rowid, sequence (name, num) including new values;

       --可以確定容器表的物理屬性

       --建立物化檢視時，查詢語句會立即把查詢的資料插入到容器表中，這是因為build immediate是預設的選項，其他選項是build deferred來制定插入記錄的時間延遲到第一次進行重新整理的時候，還有一個選項是on perbuild table 來使用一個已經存在的表作為容器表

       --查詢重寫功能在預設情況下並不啟動，要啟動需要設定enable query rewrite

       --預設情況下，為了提高fast refresh的效能會自動為容器表建立一個索引，如果不想使用可以制定using no index來實現。

       --重新整理依賴方式，可以是PRIMARY KEY也可以是ROWID。當使用的時FAST重新整理方式的時候，物化檢視日誌也需要建立為相應的ROWID或者PRIMARY KEY。

3、查詢重寫

       --全文匹配的查詢重寫：查詢最佳化器對傳遞來的查詢語句的文字與每個存在的物化檢視文字進行比較，不區分大小寫，但是字面量除外。

       --部分文字匹配的查詢重寫：雖然語句和物化檢視語句有部分差異，但是物化檢視包含了查詢所需要的所有資訊

       --一般查詢重寫：透過應用約束和維度資訊來判斷基礎表之間的關係，目的在於即使查詢語句和物化檢視語句有較大的差異也能應用物化檢視，一般情況下最佳化器不會使用未經驗證的約束，在這樣的情況下需要使用引數query_rewrite_integrity來指定一般查詢重寫可以利用未經驗證的約束。引數設定選項如下：

         Enforced：只有物化檢視是最新的資料使用查詢重寫，另外只有透過驗證的約束才能被一般查詢重寫使用。

         Trusted：只有物化檢視是最新的資料，另外維度和約束資訊在未驗證（NOVALIDATE）的情況下並且設定為rely也能使用一般查詢重寫

         Stale_tolerated：包含失效的資料的物化檢視也能被重寫，另外維度和約束資訊在未驗證（NOVALIDATE）的情況下並且設定為rely也能使用一般查詢重寫

4、  重新整理

     當表被修改的時候，依賴它的物化檢視也會失效。因此，需要執行一次重新整理來保持物化檢視的有效性，在建立物化檢視的時候，可以指定如何以及何時進行重新整理操作，方法如下:

完全重新整理—容器表中的所有內容都被刪除，然後重基表進行載入。一般要由大量資料被修改才使用這種方式。

快速重新整理---容器表中的內容會被重用，只有被修改的資料才會被同步到容器表。如果基表只有少量資料被修改，使用這樣的方式。

強制重新整理---先會嘗試快速重新整理,如果無法重新整理就嘗試完全重新整理。

永不重新整理---物化檢視永遠不重新整理。

 

可以指定以下兩種方式來指定物化檢視的重新整理的時間點

ON DEMAND---物化檢視會在請求的情況下進行重新整理，這意味著從基礎表修改到物化檢視重新整理這段時間可能有資料丟失。

ON COMMIT---物化檢視會在基礎表修改所有在的同一個事物中進行自動重新整理，就回話而言資料總是最新的。

手動重新整理---使用DBMS_view中的以下過程來完成：

Refresh—這個儲存過程用於重新整理單個或多個物化檢視，LIST用於列出名字，多個物化檢視用逗號隔開

dbms_mview.refresh_all_view(list => ‘xxx,bbbb’)

refresh_all_mviews---這個儲存過程重新整理所有的物化檢視，NUMBER_OF_FAILURES用於表示重新整理時的錯誤

dbms_mview.refresh_all_mviews(number_of_failures =>:r)

refresh_dependent---這個儲存過程用於重新整理依賴於基礎表的物化檢視，這些表透過list也傳入到列表。NUMBER_OF_FAILURES用於表示重新整理時的錯誤次數

所有儲存過程都支援method和atomic_refresh，method可以指定為c全重新整理，f快速重新整理，？強制重新整理。如果aotmic_refresh設定為false，則表示不使用單一事物，在這個時候完全重新整理會使用truncate而不是delete。

    

        如果想根據時間來自動重新整理，可以使用on demand start with ** next ** 來進行控制。

    Alter materialized view ** refresh complete on demand start with sysdate next sysdate+todsinterval(‘0 00:10:00’)，系統會自動提交一個JOB來完成這個任務。                    

 

5、  基於物化檢視日誌的快速重新整理

在快速重新整理過程中基礎表上只要發生改變才會同步到容器表上，顯然只有資料引擎知道基礎表發生了那些變化才能進行，所以必須建立一個物化檢視日誌（materialized view log）物化檢視日誌的名稱為MLOG$_後面跟基表的名稱，如果表名的長度超過20位，則只取前20位，當截短後出現名稱重複時，Oracle會自動在物化檢視日誌名稱後面加上數字作為序號。物化檢視日誌在建立時有多種選項：可以指定為ROWID、PRIMARY KEY和OBJECT ID幾種型別，同時還可以指定SEQUENCE或明確指定列名。上面這些情況產生的物化檢視日誌的結構都不相同。
任何物化檢視都會包括的列：
SNAPTIME$$：用於表示重新整理時間。
DMLTYPE$$：用於表示DML操作型別，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$：用於表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U表示UPDATE操作。
CHANGE_VECTOR$$表示修改向量，用來表示被修改的是哪個或哪幾個欄位。如果WITH後面跟了ROWID，則物化檢視日誌中會包含：
M_ROW$$：用來儲存發生變化的記錄的ROWID。如果WITH後面跟了PRIMARY KEY，則物化檢視日誌中會包含主鍵列。如果WITH後面跟了OBJECT ID，則物化檢視日誌中會包含：SYS_NC_OID$：用來記錄每個變化物件的物件ID。如果WITH後面跟了SEQUENCE，則物化檢視日子中會包含：
SEQUENCE$$：給每個操作一個SEQUENCE號，從而保證重新整理時按照順序進行重新整理。如果WITH後面跟了一個或多個COLUMN名稱，則物化檢視日誌中會包含這些列。

6、  實驗

SQL> create table lll

  2  as

  3  select * from dba_users;

SQL> create materialized view log on lll with rowid;

Materialized view log created.

SQL> create materialized view test_ll_view

  2  refresh fast with rowid

  3  as

  4  select * from lll;

Materialized view created.

SQL> drop  materialized view log on lll;

 

Materialized view log dropped.

SQL> desc MLOG$_LLL

 Name                                      Null?    Type

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

 M_ROW$$                                            VARCHAR2(255)

 SNAPTIME$$                                         DATE

 DMLTYPE$$                                          VARCHAR2(1)

 OLD_NEW$$                                          VARCHAR2(1)

 CHANGE_VECTOR$$                                    RAW(255)

SQL>  create materialized view test_ll_view_1

  2    refresh fast with rowid on commit

  3     as

  4     select * from lll;

Materialized view created

SQL> create materialized view log on lll with rowid, sequence ( USER_ID) including new values ;

 

Materialized view log created.

SQL> desc MLOG$_LLL

 Name                                                  Null?    Type

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

 USER_ID                                                        NUMBER

 M_ROW$$                                                        VARCHAR2(255)

 SEQUENCE$$                                                     NUMBER

 SNAPTIME$$                                                     DATE

 DMLTYPE$$                                                     VARCHAR2(1)

 OLD_NEW$$                                                     VARCHAR2(1)

 CHANGE_VECTOR$$                                                RAW(255)

  基於時間

 

SQL> create materialized view test_mv_1_123

  2  refresh force with rowid on demand

  3   start with sysdate next sysdate+1

  4  as

  5  select * from test_mv_row;

7、  物化檢視中的表和索引

---物化檢視首先會包含一個容器表和物化檢視的名字相同。

---如果建立了事物化檢視日誌，就多了一個日誌表，日誌表的名字為mlog$_tablename

---一般情況下在快速重新整理的情況下，會 為容器表建立一個索引來加速快速重新整理。基於ROWID的會建立在基表的M_ROW$$列上，而基於primary key 的會建立在primary key上。

實驗：

 

SQL> create table test_mv

  2  as

  3  select * from dba_users;

 

Table created.

 

SQL> create table test_mv_row

  2  as

  3  select * from dba_users;

Table created.

SQL>  alter table test_mv

  2  add constraint test_mv_pk   primary key(username);

 

Table altered.

 

SQL> create materialized view log on test_mv with primary key;

 

Materialized view log created.

 

SQL> create materialized view log on test_mv_row with rowid;

 

Materialized view log created.

SQL> create materialized view test_mv_view

  2   refresh fast with primary key on commit

  3  as

  4  select * from test_mv;

 

Materialized view created.

SQL> create materialized view test_mv_1_view

  2  refresh fast with rowid on commit

  3   as

  4   select * from test_mv_row;

 

Materialized view created.

 

 

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME

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

TEST_MV_PK                     TEST_MV                        USERNAME

TEST_MV_PK1                    TEST_MV_VIEW                   USERNAME

I_SNAP$_TEST_MV_1_VIEW         TEST_MV_1_VIEW                 M_ROW$$