[DW]An article about Materialized Views(zz)

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card= 1 Bytes=9)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off

--更改sql，不查詢owner欄位，只count，發現Oracle足夠聰明，即使沒有建立物化檢視的group語句，還是從物化檢視來訪問
SQL> set autotrace traceonly
SQL> select count(*)
2 from my_all_objects
3 where owner = 'New Owner';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card=1 Bytes=9)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off

對於事物頻繁的OLTP系統，儘量少使用物化檢視。

設定引數

如下引數可以在資料庫層或者Session層設定QUERY_REWRITE_ENABLED和
QUERY_REWRITE_INTEGRITY值。對於QUERY_REWRITE_INTEGRITY有三種值可設：
Enforced：重寫只使用資料庫中定義的約束和關係。
Trusted：除了資料庫中定義的約束和關係，Oracle還會使用其他我們告知Oracle表的某些關係，從而使得資料庫能夠重寫更多的查詢。
Stale_Tolerated：最弱的引數，及時物化檢視沒有同步更新，也會使用物化檢視來重寫SQL。

查詢重寫

全文精確匹配
如果查詢的語句與儲存在資料詞典中物化檢視字串精確匹配，那麼將會重寫查詢。這裡的精確匹配相對於共享池的比較而言更友好，它會忽略空格，大小寫以及其他的一些格式。

部分文字匹配
比較From因子後面的文字，即使Select部分的不匹配。例如：
接前面的例子，物化檢視的查詢部分的SQL：
Select owner, count(*) from my_all_objects group by owner
如下的查詢能夠被重寫：
Select lower(owner) from my_all_objects group by owner

一般性重寫方法
a. 資料滿足：查詢的資料列在物化檢視的查詢列中
b. 連線相容：查詢語句中的關聯列需要在物化檢視的查詢列中
c. 分組相容：查詢語句和物化檢視都必須有Group by語句，同時物化檢視的Group by分組層次應該高於或者等於查詢的語句。
d. 聚集相容：查詢語句和物化檢視都必須包含聚集語句。如果物化檢視包含SUM ()/COUNT () 函式，對於同樣列採用AGE () 函式進行計算可以被重寫。

確保使用物化檢視

如下的例子將會說明在怎樣的條件下可以確保採用物化檢視重寫查詢，也會比較QUERY_REWRITE_INTEGRITY值Enforced和Trusted的不同。

--這一部分語句驗證如果在資料庫中相關約束，而這種約束在定義物化檢視
--使用。那麼，如果查詢即使滿足其它被重寫的條件（資料滿足，關聯相容，
--分組相容等），也不會被重寫。

--建立測試表和物化檢視
SQL> create table emp as select * from scott.emp;
Table created.

SQL> create table dept as select * from scott.dept;
Table created.

SQL> alter session set query_rewrite_enabled=true;
Session altered.

--注意這裡使用的引數值為enforced，只有查詢中的約束和關係在物化檢視中存在，才會重寫查詢。
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.

--建立物化檢視，注意這裡是“refresh on demand”，需要手動重新整理物化檢視
SQL> create materialized view emp_dept
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select dept.deptno, dept.dname, count (*)
7 from emp, dept
8 where emp.deptno = dept.deptno
9 group by dept.deptno, dept.dname
10 /
Materialized view created.

SQL> alter session set optimizer_goal=all_rows;
Session altered.

SQL> set autotrace on

--透過執行計劃可以看到這個查詢並沒有被重寫，而是直接訪問表“EMP”。這是因為我們並沒有定義表emp和dept的之間的主外健之間的關係，這種關係物化檢視中使用，例如“emp.deptno = dept.deptno”
SQL> select count(*) from emp;

COUNT(*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82)

Statistics
----------------------------------------------------------
….略

--這一部分語句驗證增加相關約束後，查詢被重寫。

--增加相關的約束和關係
SQL> alter table dept
2 add constraint dept_pk primary key(deptno);
Table altered.

SQL> alter table emp
2 add constraint emp_fk_dept
3 foreign key(deptno) references dept(deptno);
Table altered.

SQL> alter table emp modify deptno not null;
Table altered.

SQL> set autotrace on

--由於增加了約束，Oracle能夠重寫查詢使其透過物化檢視“EMP_DEPT”來訪問資料。可以檢視下面的highlight部分的執行計劃。
SQL> select count(*) from emp;

COUNT(*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=82 Bytes=
1066)

Statistics
----------------------------------------------------------
略….

SQL> set autotrace off

--下面這部分SQL將會在比較query_rewrite_integrity在取值為Enforced和
--Trusted情況下，是否被重寫。

--刪除上述步驟建立的約束
SQL> alter table emp drop constraint emp_fk_dept;
Table altered.

SQL> alter table dept drop constraint dept_pk;
Table altered.

SQL> alter table emp modify deptno null;
Table altered.

--插入資料
SQL> insert into emp (empno,deptno) values ( 1, 1 );
1 row created.

--手工重新整理物化檢視，這是因為物化檢視建立的時候使用的是“refresh on demand”
SQL> exec dbms_mview.refresh( 'EMP_DEPT' );
PL/SQL procedure successfully completed.

--再次增加約束，但是這次增加了NOVALIDATE因子。這樣即使資料不符合約束也能夠成功建立。
SQL> alter table dept
2 add constraint dept_pk primary key(deptno)
3 rely enable NOVALIDATE
4 /
Table altered.

SQL> alter table emp
2 add constraint emp_fk_dept
3 foreign key(deptno) references dept(deptno)
4 rely enable NOVALIDATE
5 /
Table altered.

SQL> alter table emp modify deptno not null NOVALIDATE;
Table altered.

SQL> set autotrace on

--設定query_rewrite_integrity值為enforced
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.

--檢查增加約束但資料不滿足約束的情況下，如果是query_rewrite_integrity的值為enforcedenforced，那麼物化檢視不會被利用來重寫查詢。
SQL> select count(*) from emp;
COUNT(*)
----------
15

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=164)

Statistics
----------------------------------------------------------
略….

--設定query_rewrite_integrity值為trusted
SQL> alter session set query_rewrite_integrity=trusted;
Session altered.

--檢查增加約束但資料不滿足約束的情況下，如果是query_rewrite_integrity的值為trusted，那麼物化檢視將會被利用來重寫查詢。
SQL> select count(*) from emp;
COUNT(*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=82 Bytes=
1066)

Statistics
----------------------------------------------------------
略….

Dimension
建立緯度對映不同列之間的父子關係，類似於在discoverer中建立的維度，使得在查詢的時候根據需求下鑽上卷。在這裡可以為Oracle提供更多的資訊，從而使得重寫查詢的可能性增大。非常類似discoverer中的hierarchy的概念。

create dimension sales_dimension
--這一部分定義類似於資料庫欄位的別名
level cust_id is customer_hierarchy.cust_id
level zip_code is customer_hierarchy.zip_code
level region is customer_hierarchy.region
level day is time_hierarchy.day
level mmyyyy is time_hierarchy.mmyyyy
level qtr_yyyy is time_hierarchy.qtr_yyyy
level yyyy is time_hierarchy.yyyy
--定義其中一個層次結構
hierarchy cust_rollup
(
cust_id child of
zip_code child of
region
)
--定義另外一個層次結構
hierarchy time_rollup
(
day child of
mmyyyy child of
qtr_yyyy child of
yyyy
)
--mmyyyy和mon_yyyy是同義詞
attribute mmyyyy
determines mon_yyyy;

DBMS_OLAP
透過DBMS_OLAP，可以完成如下工作：
a. 估算物化檢視的大小
b. 驗證維度物件是否有效
c. 建議建立起它物化檢視，找出需要刪除的檢視並重新命名
d. 評估物化檢視的使用狀況