Oracle如何使用物化視圖查詢重寫query rewrite

這篇文章主要介紹了Oracle如何使用物化視圖查詢重寫query rewrite，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

1.概念
容器表： 建立MATERIALIZED VIEW時(shí)自動(dòng)創(chuàng)建、實(shí)際儲(chǔ)存數(shù)據(jù)的物理表，與物化視圖同名的table
基礎(chǔ)表： 建立物化視圖語(yǔ)句as select ... 中引用到的table

刷新方式
refresh fast: 容器表中的數(shù)據(jù)會(huì)被重用，只有基礎(chǔ)表被修改的數(shù)據(jù)才會(huì)同步到容器表
refresh complete: 容器表中的數(shù)據(jù)會(huì)被全刪除，基礎(chǔ)表所有數(shù)據(jù)全面同步到容器表
refresh force:先嘗試refresh fast，如果失敗執(zhí)行refresh complete
never refresh:永不刷新

刷新頻率
on demand: 顯示的指定刷新，可以手動(dòng)刷新或者按照指定的間隔時(shí)間刷新
on commit: 在基礎(chǔ)表同一個(gè)transaction中刷新，即基礎(chǔ)表數(shù)據(jù)變化就刷新

query rewrite（查詢重寫）
想提高程式效率，SQL經(jīng)常執(zhí)行，但不能改寫SQL（通常是多表連接），SQL語(yǔ)句上又不好進(jìn)步一優(yōu)化的，可以考慮使用query rewrite 提高性能。
如使用，關(guān)注動(dòng)態(tài)參數(shù)有兩個(gè)
query_rewrite_enabled：
默認(rèn)值true 啟用查詢重寫
query_rewrite_integrity：
enforced 只有物化視圖數(shù)據(jù)是最新，且約束被驗(yàn)證(validate)才能使用到查詢重寫，是默認(rèn)值
trusted 只有物化視圖數(shù)據(jù)是最新，且約束未驗(yàn)證(novalidate)但要標(biāo)誌為信任(rely)才能使用到查詢重寫
stale_tolerated 即使物化視圖數(shù)據(jù)不是最新，也可以使用到查詢重寫

2.query rewrite使用案例
下列SQL要經(jīng)常執(zhí)行，執(zhí)行效率不高，SQL語(yǔ)句無(wú)法優(yōu)化，且table數(shù)據(jù)變化不多
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，在基礎(chǔ)表數(shù)據(jù)變化時(shí)快速刷新
創(chuàng)建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;
創(chuàng)建報(bào)錯(cuò)：ORA-23413: 表格 "MES1"."EMP1" 沒(méi)有具體化視觀表日誌
說(shuō)明：
因?yàn)閞efresh fast時(shí)基表必須有物化視圖log
處理：
create MATERIALIZED VIEW LOG ON mes1.emp with rowid;
create MATERIALIZED VIEW LOG ON mes1.emp1 with rowid;
再次執(zhí)行創(chuàng)建報(bào)錯(cuò)ORA-01031: 權(quán)限不足
說(shuō)明：
此處執(zhí)行雖然是sys賬號(hào)，但實(shí)為mes1賬號(hào)沒(méi)有create table權(quán)限，具體分析可以參考 http://blog.itpub.net/4227/viewspace-310155/
處理：
grant CREATE table to mes1;
再次執(zhí)行創(chuàng)建報(bào)錯(cuò)ORA-12052: 無(wú)法快速重新整理具體化視觀表 MES1.MV_EMP
說(shuō)明：
原來(lái)是定義中沒(méi)有加上使用到基礎(chǔ)表的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
再次執(zhí)行創(chuàng)建：
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 |
---------------------------------------------------------------------------------------

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Oracle如何使用物化視圖查詢重寫query rewrite”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!