[轉載]oracle物化視圖

標籤：

原文URL：http://lzfhope.blog.163.com/blog/static/636399220124942523943/?suggestedreading&wumii

環境oracle 10g 10.2.0.4  linux 64

要大而專業的看oracle自己的文檔-sql參考。

說實話，oracle需要學習的內容太多，每個都看過去，實在太費事。

所以如果能夠對物化視圖有個概覽，那最方便不過。

 

主要涉及內容

物化視圖日誌，用於快速重新整理所必須的

物化視圖

許可權，通常不是個難題，因為編譯的時候會給你足夠的許可權提示，沒有的話找dba.為了練習，盡可以在測試環境中賦予dba的許可權。

 

什麼是物化視圖？（物化視圖的簡單定義）

簡而言之，就是具有實體表的視圖，而且這個視圖還可以根據多種需求和策略進行重新整理。此外還有一個非常重要的功能-查詢重寫（query rewrite)  .查詢重寫能夠在某些時候提高你的查詢速度。

所謂查詢重寫，簡而言之，就是oracle 的查詢最佳化工具發現有個物化視圖的文法和你的SQL差不多，那麼就會直接存取物化視圖，而不是你原來查詢中有關的源表。

 

物化視圖能幹什嗎？

或者說，你能拿物化視圖做什麼用。

前文簡單說了下，此處列出一些重要而想詳細的功能：

1)能夠提高查詢速度，這主要是因為物化視圖儲存了實際的資料，其次具有查詢重寫功能。最後，物化視圖具有實體表，你也可以在上面建立索引，總之大體上當作一個表用就可以了。

2) 簡化了開發工作單位,意思是開發的人員有的時候，無需直接關注部分sql的效能，而通過dba的努力，使用查詢重寫來完成效能的提升。

3)減少了工作量，因為物化視圖可以定義兩種重新整理方式：立即重新整理，按需重新整理。所謂按需重新整理就是你自己手動重新整理，或者是定時重新整理；所謂立即重新整理，即視圖主表發生變化的時候，視圖立即重新整理內容。  你可以根據自己的裝置情況，應用情況和需求來控制重新整理的方式。

4)重新整理量的靈活限制，你可以快速是重新整理（只重新整理變化的），也可以全重新整理。看你的需要。

 

物化視圖的文法？

略，這個內容太多，還是去看oracle官方的sql參考吧！

 

物化視圖有關的參數

主要是最佳化參數（查詢重寫的）

SQL> show parameters query   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ query_rewrite_enabled                string      TRUE query_rewrite_integrity                string      enforced

第一個參數是表示是否支援查詢重寫，預設是可以

第二個參數是查詢重寫的支援方式：

STALE_TOLERATED：表示即使細目表中的資料已經發生了變化，也仍然使用物化視圖。  TRUSTED ：表示物化視圖未失效時才使用該視圖。但是，查詢改寫可以使用信任關係，如那些由維度對象或尚未生效的約束所聲明的關係。  ENFORCED（預設）:表示當物化視圖保證能給出與使用細目表相同的結果時才使用它。使用這一參數意味著查詢改寫將不使用失效的物化視圖或信任關係。  正確的設定決定於應用程式的資料需求。使用失效物化視圖的查詢改寫可能會產生與沒有使用查詢改寫時不同的結果。然而，如果使用細目資料，可能會因為響應查詢需要處理的大量資料而使效能惡化。在一個資料倉儲中，通常使用TRUSTED完整層級，因為這樣才可以保證你只使用那些具有最新資料的物化視圖；然而，被聲明為正確（可信任）的關係也可用於查詢改寫。在大多數資料倉儲中，這些關係已經在擷取、轉換和下載（ETL）過程得到了驗證，因此不再需要進行驗證。 

 

物化視圖有關的工具

主要是dbms_mview包。

對於初學者，主要關心兩個過程:

explain_mview,看有關sql是否支援物化視圖的有關功能。

Explain_Rewrite，看有關查詢sql是否支援查詢重寫。

explain_mview需要有個表：mv_capabilities_table

SQL> desc mv_capabilities_table Name            Type           Nullable Default Comments  --------------- -------------- -------- ------- --------  STATEMENT_ID    VARCHAR2(30)   Y                          MVOWNER         VARCHAR2(30)   Y                          MVNAME          VARCHAR2(30)   Y                          CAPABILITY_NAME VARCHAR2(30)   Y                          POSSIBLE        CHAR(1)        Y                          RELATED_TEXT    VARCHAR2(2000) Y                          RELATED_NUM     NUMBER         Y                          MSGNO           INTEGER        Y                          MSGTXT          VARCHAR2(2000) Y                          SEQ             NUMBER         Y

這可以在$ORACLE_HOME/rdbms/admin/utlxmv.sql找到指令碼

explain_rewrite需要表格：rewrite_Table

指令碼同樣在 $ORACLE_HOME/rdbms/admin/utlxrw.sql中。

除了這些，還有許多的過程，例如:

select * from dba_procedures  where PROCEDURE_NAME LIKE ‘%MVIEW%‘

select * from dba_procedures  where OBJECT_NAME=‘DBMS_SNAPSHOT‘

(DBMS_ MVIEW是DBMS_SNAPSHOT同義字）select * from dba_synonyms where synonym_name like ‘%DBMS_MVIEW%‘

（這意思是10g以後大家不要再叫物化視圖為快照了).

 

物化視圖日誌簡介

物化視圖的作用就是為了支援快速重新整理

總體上文法比較簡單，可以簡化為如下：

create materilized view log on  xxxx  with ?  [including|excluding] new values

其中including之後的new values可以不要。

log的重點是with和new values兩個句子

with字句是告訴oracle當主表資料變化的時候是否需要記錄主鍵、行號、對象ID，或者是這些標識的組合資訊，大略的文法圖如下：

 object id: 是系統產生或者使用者定義物件標識符，如果源表行有變化，那麼就記錄對象的ID。當然只有源表是對象表，你才能這麼設定。

 primary key: 行變化的時候，儲存的是主鍵.。

rowid:變化時候，儲存行號.

sequence: 指示哪些額外的排序資訊需要儲存起來。序列指對於一些一些更新情境的快速重新整理有用。

column: 指示哪些列的指需要儲存起來（到日誌中）。通常這些列是過濾用或者是串連列。

with語句的限制：

1）每個物化視圖日誌只能設定一個primary key,rowid,object id,sequcen，以及列列表，或者說with 語句後這些關鍵字只能更上一次，不能這些 with primary key  ,primary key

2)主鍵列已經是隱式儲存在日誌中，因此，主鍵列就不能包含在 column語句中了。

 

new values 語句

new values語句指示oracle在帶更新的DML操作中儲存舊資料和新資料到日誌中。預設情況，是不記錄新新的值。

樣本：

create materialized view log on customers with primary key,rowid;  --支援rowid物化視圖和join物化視圖。

create materialized view log on sales with rowid, sequence(amount_sold,time_id,prod_id) including new values; 支援帶統計的物化視圖（amount_sold是被sum的列，time_id,prod_id是用於過濾的列）。

例如視圖： CREATE MATERIALIZED VIEW products_mv REFRESH FAST ON COMMIT AS SELECT SUM(list_price - min_price), category_id FROM product_information GROUP BY category_id;

 

物化視圖例子：

 這裡給出了一個能夠快速及時更新的物化視圖例子，比較簡單。

例子1：提交的快速重新整理物化視圖

create table test_1(id int,name varchar2(20),    constraint pk_test_1 primary key(id) )

create table test_2(id int,score int,   constraint pk_test_2 primary key(id)   )    drop materialized   view log on   test_1 drop materialized   view log on   test_2 create materialized view log  on test_1 with rowid create materialized view log  on test_2 with rowid

create materialized view mv_fastrefresh  refresh fast on commit with rowid enable query rewrite as  select a.id,b.name,a.score,a.rowid ra,b.rowid rb      from test_2 a, test_1 b where b.id=a.id

 

例子二：快速重新整理的統計物化視圖

create table test_s1(id int,name varchar2(20),    constraint pk_test_s1 primary key(id) )

create table test_s2(id int,mon int, salary number,   constraint pk_test_s2 primary key(id,mon)   )    drop materialized   view log on   test_s1; drop materialized   view log on   test_s2; create materialized view log  on test_s1 with rowid ,sequence(id,name) including new values; create materialized view log  on test_s2 with rowid ,sequence(id,salary) including new values;

create materialized view mv_fastrefresh_sum  refresh fast  with primary key enable query rewrite as  select a.id,b.name,sum(a.salary) totalIncome     from test_s2 a, test_s1 b where b.id=a.id    group by a.id,b.name

 

補充

2012/05/21

start with or next 不能和on demand ,on commit共存

------------------------------------------------------- 2013、04、18 由於工作需要，經常要編寫這樣的指令碼，故保留下： --先檢查，然後有必要就刪除 declare   vs_jobname varchar2(30):=‘REFRESH_DMART_JYR‘;   VN_COUNT PLS_INTEGER; begin   /*     建立的時候重新整理一次，以後每年12月前後一周的每天晚上23：53進行重新整理‘   */   SELECT COUNT(*) INTO VN_COUNT     FROM USER_SCHEDULER_JOBS     WHERE JOB_NAME=VS_JOBNAME;        IF VN_COUNT>0 THEN     DBMS_SCHEDULER.DROP_JOB(VS_JOBNAME,TRUE);   END IF;   DBMS_SCHEDULER.create_job(job_name =>VS_JOBNAME      ,job_type => ‘PLSQL_BLOCK‘     ,job_action => ‘BEGIN  DBMS_MVIEW.REFRESH(‘‘MV_DISTINCT_JYR‘‘,‘‘c‘‘);END;‘     ,start_date => SYSTIMESTAMP     ,repeat_interval => ‘FREQ=YEARLY; BYDATE=1231^SPAN:1W;BYHOUR=23;BYMINUTE=53‘     ,enabled => TRUE     ,auto_drop => FALSE     ,comments => ‘重新整理系統交易日‘   );  DBMS_SCHEDULER.run_job(vs_jobname);  end; 

[轉載]oracle物化視圖