Oracle如何使用物化视图查询重写query rewrite

这篇文章主要介绍了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-/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 |

---------------------------------------------------------------------------------------

感谢你能够认真阅读完这篇文章，希望小编分享的“Oracle如何使用物化视图查询重写query rewrite”这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!