物化檢視學習筆記

物化檢視和檢視從字面上來看就差兩個字，可是差兩個字就複雜很多了，

所以打算整理下自己物化檢視方面的知識，讓自己加深對物化檢視的理解。

什麼是物化檢視及物化檢視的特性

物化檢視（materialized view）是一個schema 物件。可以用於彙總，計算，

複製，分發資料。物化檢視中儲存了SQL 語句的查詢結果。

1、物化檢視與檢視一個很重要的不同在於物化檢視是實際的儲存SQL語句的結果，

需要消耗相應的儲存空間，所以建立物化檢視的使用者需要有足夠的表空間配額。

2、物化檢視可以根據一定的策略在master table 改變以後refresh。

3、如果一個SQL 查詢可以重寫到一個物化檢視，物化檢視可以提高查詢語句的效能。

4、物化檢視對運用程式和使用者是透明的。

物化檢視使用的場景

1、在資料倉儲環境下，可以使用物化檢視儲存來自於聚集函式的計算結果。比如sum,

avg.這樣透過對物化檢視的查詢，可以提高響應時間，因為物化檢視中的資料是預先

計算好的，或者是查詢物化下來的join 結果集。

2、物化檢視可以用在複製，所以物化檢視時常被稱作快照（snapshot）。

oracle 8i開始snapshot 被重新定義為物化檢視，在語法方面也做了改動。

透過database link 可以把遠端的master database 中的master tables 中的資料

的一部分或者全部複製到本地，這很適合於客戶端不經常連線到網路中的情形

，方便本地訪問資料，並且本地所在的更新也可以同步到master database。

3、在移動計算環境下，可以透過物化檢視，定期的從中央伺服器下載資料

到移動客戶端，並上傳客戶端所在的更新。

我覺得後面兩種情形都是複製特性的運用。

建立物化檢視前的準備工作

1、在自己的schema 下建立物化檢視。你需要獲得create materialized view、

create table 系統許可權。所有master table 的select 許可權。

2、在其他使用者的schema 下建立物化檢視。你需要create any materialized view

系統許可權。物化檢視的所有者需要具備create table 系統許可權和所有master table

的select 許可權。

注:

1、建立物化檢視所需要的許可權不能透過role 授權給user，而是要直接的授權給user.

2、為了能夠建立refresh-on-commit 的物化檢視，使用者必須獲得所有master table 的

on commit refresh 物件許可權。

3、物化檢視的所有者應該具備在相應表空間上足夠的空間配額，以便儲存物化檢視。

4、建立可以被query rewrite 的物化檢視，還需要額外的許可權，請參考：

*******************************************************************

關於建立物化檢視許可權的示例。

SQL> conn hr/hr
Connected.

SQL> create materialized view emp_mv
2 refresh fast next sysdate
3 as select employee_id,last_name,first_name
4 from employees;
from employees
*
ERROR at line 4:
ORA-01031: insufficient privileges

SQL> show user
USER is "SYS"

SQL> grant create materialized view to hr;
Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> create materialized view emp_mv
2 refresh fast next sysdate
3 as select employee_id,last_name,first_name
4 from employees;
Materialized view created.

*****************************************************************

物化檢視refresh 的方式

當master table 更新以後，可以透過以下兩種方式來refresh 物化檢視。

1、complete refresh

這種方式的refresh 需要在定義物化檢視的時候透過build immediate 指定。

這種方式的refresh 實際上是重新執行一遍該物化檢視的定義的查詢。

當master table 很大的時候，因為需要訪問很多的大量的實際，所有速度

將會變的很慢。

2、fast refresh

這種方式的refresh 不需要從頭開始構建物化檢視，而是透過增量的方式，

僅僅運用refresh 改變的那一部分，所有效率非常的高。為了支援fast refresh

需要建立materialized view logs 用來跟蹤改變(insert,update,delete)。這個

很想redis 裡面的aof 檔案。materialized view logs 和master table 是在

同一個schema 中的。

***************************************************************
關於refresh的示例。

SQL> create materialized view log on employees;--先要建立物化檢視log。
Materialized view log created.

SQL> create materialized view emp_mv--建立一個物化檢視指定refresh 的方式和時間。
2   refresh fast next sysdate
3   as select employee_id,last_name,first_name
4   from employees;
Materialized view created.

SQL> select * from emp_mv
2 where employee_id = 100;
EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ---------- ----------
100 King Steven

SQL> update employees--對master table 做一個更新。
2 set last_name = upper(last_name)
3 where employee_id = 100;
1 row updated.

SQL> select * from emp_mv--未提交的時候物化檢視和master table 不同步。
2 where employee_id = 100;
EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ---------- ----------
100 King Steven

SQL> commit;
Commit complete.
SQL> select * from emp_mv--物化檢視和master table 同步。
2 where employee_id = 100;
EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ---------- ----------
100 KING Steven

上面建立的物化檢視指定的refresh 方式是fast refresh,時間是實時的同步。

下面建立一個refresh fast on commit 方式的物化檢視.

我們已經在master table 上建立過materialized view log所以不需要再建立。

SQL> create materialized view emp_mv1
2   refresh fast on commit
3   as select employee_id,last_name,first_name
4   from employees;

Materialized view created.
SQL> select * from emp_mv1
2 where employee_id = 100;
EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- -------------- --------------
100 King Steven

在master table 中執行一個更新。
SQL> update employees
2 set last_name = upper(last_name)
3 where employee_id = 100;
1 row updated.

沒有提交前檢視下emp_mv1 。
SQL> select * from emp_mv1
2   where employee_id = 100;
EMPLOYEE_ID LAST_NAME      FIRST_NAME
----------- -------------- --------------
        100 King           Steven

提交以後會馬上以fast refresh 方式重新整理物化檢視。
SQL> commit;
Commit complete.
SQL> select * from emp_mv1
2   where employee_id = 100;
EMPLOYEE_ID LAST_NAME      FIRST_NAME
----------- -------------- --------------
        100 KING           Steven

*****************************************************************

關於物化檢視自動query rewrite 的示例。

這裡說的是自動查詢重寫，而不是在from 子句中指定查詢的物化檢視。query rewrite是最佳化器的一項功能，最佳化器將會在語義一致的前提下使用帶有物化檢視的執行計劃。

SQL> set autotrace trace explain
SQL> select employee_id,last_name,first_name
2 from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |   108 | 2052 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| EMPLOYEES |   108 | 2052 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

不重寫，why？因為我們建立emp_mv1 的時候沒有指定enable query rewrite.可以透過下面的語句實現enable query rewrite 。

SQL> alter materialized view emp_mv1 enable query rewrite;
Materialized view altered.

SQL> exec dbms_stats.gather_table_stats(user,'EMP_MV1');
PL/SQL procedure successfully completed.
SQL> SET LINES 120
SQL> select employee_id,last_name,first_name
2 from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2814234707
----------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   108 | 1944 |     3   (0)| 00:00:01 |
|   1 | MAT_VIEW REWRITE ACCESS FULL| EMP_MV1 |   108 | 1944 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

當我們開啟enable query write 的時候，我們發現對employees 的查詢自動的重寫為對物化視emp_mv1 的查詢。

********************************************************************************

刪除物化檢視及物化檢視logs

SQL> drop materialized view emp_mv1;
Materialized view dropped.

SQL> drop materialized view log on employees;
Materialized view log dropped.

注：一個master table 上面只能建立一個materialized view log 物件。

小結：

整理了這些物化檢視的知識，雖然腦袋知識條理清楚了很多。但是還有很多關於物化檢視方面的知識需要學習，加油！

物化檢視學習筆記

相關文章