Expert One-on-One Oracle Chapter 13 物化視圖 讀書筆記(3)

物化視圖的最大的優勢是可以提高效能：通過預先計算好答案儲存起來，可以大大地減少機器的負載。
特點如下：
更少的物理讀--掃描更少的資料
更少的寫--不用經常排序和聚集
減少CPU的消耗--不用對資料進行聚集計算和函數調用
顯著地加快回應時間--在使用物化視圖查詢資料時(與主表相反)，將會很快的返回查詢結果

物化視圖會增加對磁碟資源的需求，即需要永久分配的硬碟空間給物化視圖來儲存資料。
物化視圖用於唯讀或者“精讀”環境下工作最好 ，不用於聯機交易處理系統(OLTP)環境。

下面講物化視圖是如何工作的？
假設建立了一個物化視圖，並且知道該物化視圖有了某個問題的答案，但是由於某些原因，oracle沒有這樣做。
為什麼ORACLE不會知道使用物化視圖可以知道答案？
是因為oralce他不知道一些資訊，一些能夠告訴它擷取我們想要的資訊的資訊。
oracle只是一個軟體，她僅僅能對提供給它的資訊進行處理。提供的中繼資料越多，給oracle有關潛在的資料的資訊越讀，則越好。這些資訊
可能是一些約束，主鍵，外鍵等等。

下面將講些例子來說明物化視圖必須做什麼，以及提供給它資訊物化視圖將會如何會用的更多。
 物化視圖的設定：
 系統級：通過INIT.ORA
 會話級：通過alter session命令
 設定參數如下：
 query_rewrite_enabled--此參數設定為true，則表示會發生查詢重寫；當為false，則不會發生查詢重寫；
 query_rewrite_integrity--此參數是控制oracle如何重寫查詢，且可能設定為下面的三種值：
   enforced--僅僅使那些由oracle強迫和保證的約束以及規則的查詢可以重寫；
             但由於oralce不強加一些可以通過oralce知道其他的推論關係的查詢的關係；
   trusted--使用oralce強加的約束，以及任意關係存在於資料中的而非資料庫強加的關係可以重寫查詢；
   stale_tolerated-使用物化視圖可以重寫查詢，即使oralce知道包含的資料是陳舊的。

  query_rewrite_enabled=false,oracle對SQL語句進行分析與最佳化；
  query_rewrite_enabled=true，oracle通過起用查詢重寫功能，在分析過SQL語句之後，oracle將多一個步驟重寫此查詢以訪問某些物化視圖，而不是它所參照的真正的表。
                              如果能夠執行查詢重寫，這些重寫的查詢就被分析，並與初始查詢一起最佳化，從資料字典所發現的可利用的物化視圖的集合中執行成本最低
                              的查詢方案，這樣的查詢成本最低；
                              如果不能重寫此查詢，就對原始分析的查詢進行最佳化，並正常運行；
1、查詢重寫的步驟：
  完全精確的本文匹配->部分本文匹配->一般查詢重寫方法（要求資料充足並且串連相容）->分組相容性->聚集相容性

2、如何確保物化視圖可以使用？
  這裡只講三種方法來協助使用物化視圖的查詢重寫功能：
  1）約束；
  2）維數；
  3）描述複雜關係-資料層次結構；
 
下面是對上面三種方法舉例說明：
1）約束：
SQL> create table emp as select * from scott.emp;
Table created.
Elapsed: 00:00:06.41
SQL> create table dept as select * from scott.dept;
Table created.
Elapsed: 00:00:01.02

SQL>grant query rewrite to  test;
Grant succeeded.
Elapsed: 00:00:01.06

SQL> alter session set query_rewrite_enabled=true;
Session altered.
Elapsed: 00:00:00.22

SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
Elapsed: 00:00:00.01

SQL> create materialized view emp_dept
  2  build immediate
  3  refresh on demand
enable query rewrite
  4    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;

SQL> alter session set optimizer_goal=all_rows;
Session altered.
Elapsed: 00:00:00.05
（註：All Rows:也就是我們所說的Cost的方式,當一個表有統計資訊時,它將以最快的方式返回表的所有的行,從總體上提高查詢的輸送量。
                沒有統計資訊則走基於規則的方式。）

此時oralce並不知道EMP表和DEPT表之間的餓關係，不知道哪一列是住碼等。
SQL> set autotrace on

SQL> select count(*) from emp;

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

Elapsed: 00:00:03.29

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=327)

 

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

實質上面的SQL語句是可以採用查詢重寫中的分組相容性步驟來實現採用物化視圖來查詢，因為此時可以很容易的從物化視圖中擷取資訊；
但是oralce不知道emp與dept表之間的關係，emp表中的每個員工都上屬於DEPT表中的部門，所以oracle沒有採用物化視圖。
需要我們告訴oralce,下面是告知oracle emp表和 DEPT表之間的關係：
SQL> alter table dept
add constraint dept_pk primary key(deptno);  2      --告訴oracle deptno欄位是dept表的主碼

Table altered.

Elapsed: 00:00:03.54
SQL> alter table emp            --告訴oracle emp表中的deptno欄位是一個外碼，與dept表中的deptno有對應關係
add constraint emp_fk_dept
  2    3  foreign key(deptno) references dept(deptno);

Table altered.

Elapsed: 00:00:00.81
SQL> alter table emp modify deptno not null;  --告訴oracle emp表中的deptno欄位是不為空白的

Table altered.

Elapsed: 00:00:00.24

告訴oracle這些資訊之後，再執行select count(*) from emp語句：
SQL> set autotrace on
SQL> select count(*) from emp;

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

Elapsed: 00:00:01.20

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=327 Bytes
          =4251)

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

從上面的執行計畫中看出此時oracle採用了物化視圖來查詢，oracle能夠進行查詢重寫；

上面的例子是要求要告訴oracle資料庫emp表和dept表之間的約束關係，但是有時候不想在另外花力氣來核實這個外碼的關係，
已經在資料淨化常式中做了這件事（什麼叫資料淨化？在資料倉儲的組成中有一個叫“資料抽資料淨化資料載入”，可能就是這
裡提到的資料淨化，意思可能就是在資料被載入到資料庫之前，已經進行了資料的淨化，不會有資料不完整性的情況存在）。
下面舉例來類比這個資料裝入資料庫的過程，
先已經對資料進行的了淨化，然後丟掉約束，裝入資料，重新整理物化視圖，最後再將約束加進去。
先從丟掉約束開始：
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.

SQL> commit;

Commit complete.

下面是告訴oracle重新整理物化視圖：
SQL> exec dbms_mview.refresh( 'EMP_DEPT' );

PL/SQL procedure successfully completed.

這個時候告訴oracle有關emp和dept表之間的關係：
SQL> alter table dept
add constraint dept_pk primary key(deptno)
  2    3  rely enable NOVALIDATE ;

Table altered.

SQL> alter table emp
add constraint emp_fk_dept
foreign key(deptno) references dept(deptno)
rely enable NOVALIDATE  2    3    4  ;

Table altered.

SQL> alter table emp modify deptno not null NOVALIDATE;

Table altered.

上面的語句中有rely enable NOVALIDATE以及NOVALIDATE表示告訴oracle不必不再對裝入的已有資料的檢查，並且rely告訴oracle要相信
資料的完整性，即告知oracle相信如果將emp和dept表連起來， 將檢索emp表的每一行。
事實上對oracle說的並不是事實，有一條新插入emp的資料在dept中並沒有對應的行，違反了資料的完整性。
如果設定alter session set query_rewrite_integrity=enforced;
則下面：
SQL> alter session set query_rewrite_integrity=enforced;

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*) from emp;

  COUNT(*)
----------
        15

Elapsed: 00:00:00.40

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=654)

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

分析：當query_rewrite_integrity=enforced時，只有使那些由oracle強迫
      和保證的約束以及規則的查詢才可以重寫 ，上面儘管建了約束，但是這個約束並沒有
      核實，而且並沒有被資料庫自己所確認，所以該語句並沒有查詢重寫rely enable NOVALIDATE以及NOVALIDATE
      表示告訴oracle不必不再對裝入的已有資料的檢查，實質就是資料庫自己並沒有確認，所以並不是
      由oracle強迫和保證的約束以及規則的查詢
      
      
SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select count(*) from emp;

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

Elapsed: 00:00:00.30

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
----------------------------------------------------------
         20  recursive calls
          1  db block gets
         17  consistent gets
          1  physical reads
        100  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
         
設定了query_rewrite_integrity=trusted，告訴oracle使用oralce強加的約束，
以及任意關係存在於資料中的而非資料庫強加的關係可以重寫查詢，設定過後資料庫
認為資料中有非資料庫強加的emp和dept表之間的關係存在，則資料庫認為可以採用
查詢重寫，使用物化視圖。此時oracle對新插入的資料認為是違反了的emp和dept表之間
約束的關係，所以並沒有對新插入的行的做記數，及時物化視圖做重新整理，也沒有將新插入
的資料加到物化視圖中去，oracle認識到我們告訴他的資料不可靠， 可以知道：
1、對於大型資料倉儲可以非常相信物化視圖，不必自己再做的量資料的校正；
2、如果你要你想要的資料，必須自己一確保資料的可靠，100%的淨化了資料；

講了半天才將自己講懂。