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

162次閱讀

共計 2837 個字符，預計需要花費 8 分鐘才能閱讀完成。

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

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 |
—————————————————————————————

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“Oracle 如何使用物化視圖查詢重寫 query rewrite”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!

正文完