Oracle最佳化技術---物化檢視查詢重寫query rewrite

1.概念
容器表： 建立MATERIALIZED VIEW時自動建立、實際儲存資料的物理表，與物化檢視同名的table
基礎表： 建立物化檢視語句as select ... 中引用到的table

重新整理方式
refresh fast: 容器表中的資料會被重用，只有基礎表被修改的資料才會同步到容器表
refresh complete: 容器表中的資料會被全刪除，基礎表所有資料全面同步到容器表
refresh force:先嘗試refresh fast，如果失敗執行refresh complete
never refresh:永不重新整理

重新整理頻率
on demand: 顯示的指定重新整理，可以手動重新整理或者按照指定的間隔時間重新整理
on commit: 在基礎表同一個transaction中重新整理，即基礎表資料變化就重新整理

query rewrite（查詢重寫）
想提高程式效率，SQL經常執行，但不能改寫SQL（通常是多表連線），SQL語句上又不好進步一最佳化的，可以考慮使用query rewrite 提高效能。
如使用，關注動態引數有兩個
query_rewrite_enabled：
預設值true 啟用查詢重寫
query_rewrite_integrity：
enforced 只有物化檢視資料是最新，且約束被驗證(validate)才能使用到查詢重寫，是預設值
trusted 只有物化檢視資料是最新，且約束未驗證(novalidate)但要標誌為信任(rely)才能使用到查詢重寫
stale_tolerated 即使物化檢視資料不是最新，也可以使用到查詢重寫

2.query rewrite使用案例
下列SQL要經常執行，執行效率不高，SQL語句無法最佳化，且table資料變化不多
select a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name
from mes1.emp a,mes1.emp1 b
where a.emp_no=b.emp_no

考慮使用物化檢視 query rewrite功能，使用refresh fast on commit，在基礎表資料變化時快速重新整理
建立MV:
CREATE MATERIALIZED VIEW mes1.mv_emp
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
AS
select a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name 
from mes1.emp a,mes1.emp1 b
where a.emp_no=b.emp_no;
建立報錯：ORA-23413: 表格 "MES1"."EMP1" 沒有具體化視觀表日誌
說明：
因為refresh fast時基表必須有物化檢視log
處理：
create MATERIALIZED VIEW LOG ON mes1.emp with rowid;
create MATERIALIZED VIEW LOG ON mes1.emp1 with rowid;
再次執行建立報錯ORA-01031: 許可權不足
說明：
此處執行雖然是sys賬號，但實為mes1賬號沒有create table許可權，具體分析可以參考 http://blog.itpub.net/4227/viewspace-310155/
處理：
grant CREATE table to mes1;
再次執行建立報錯ORA-12052: 無法快速重新整理具體化視觀表 MES1.MV_EMP
說明：
原來是定義中沒有加上使用到基礎表的rowid
處理：
SELECT a.ROWID arowid, b.ROWID browid,a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name 
from mes1.emp a,mes1.emp1 b where a.emp_no=b.emp_no
再次執行建立：
CREATE MATERIALIZED VIEW mes1.mv_emp
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
AS
   SELECT a.ROWID arowid,b.ROWID browid,
   a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name 
   from mes1.emp a,mes1.emp1 b where a.emp_no=b.emp_no ;
成功！！

最後看下query rewrite 效果：
SQL> set trace traceonly;
SQL> SELECT a.emp_no aemp_no,a.emp_name aemp_name,
  2            b.emp_no bemp_no,b.emp_name bemp_name
  3      FROM mes1.emp a, mes1.emp1 b  WHERE a.emp_no = b.emp_no;
Execution Plan
----------------------------------------------------------
Plan hash value: 2244303076
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     3 |   120 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_EMP |     3 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------