Oracle 物化檢視1 - 單表聚合及其快速重新整理

chncaesar發表於2013-12-09
Oracle

簡介

物化檢視在資料倉儲中常用，將結果預先計算好並儲存在物化檢視中，Oracle資料庫通過Query Rewrite訪問物化檢視。可以提高SQL反應速度，改善使用者體驗。整個過程對使用者是透明的。對於每個物化檢視，Oracle會自動建立一個同名表來儲存物化檢視的資料。

物化檢視支援兩種重新整理方式：
Fast Refresh
Complete Refresh.
Fast refresh的好處在於增量式更新MV，而Complete Refresh需要重新執行MV的Select語句。一個資料量大的基表一次Complete Refresh比Fast Refresh需要更多時間。

物化檢視的統計資訊

每一個物化檢視都有一個同名的表。收集物化檢視的統計資訊就是收集這個表的統計資訊。例如，物化檢視T_AGG
exec dbms_stats.gather_table_stats(,'T_AGG', cascade=>true)


聚合式物化檢視Fast Refresh的條件：

1. 基表上建立MV Log，MV log必須包含：

Rowid
所有在MV中出現的列
INCLUDING NEW VALUES
Sequence

2. MV的select包含
count(*)
對於每個做了聚合的列，都必須有count()

更多條件請見：Oracle Data Warehousing Guide第九章Basic Materialized Views的Restrictions on Fast Refresh on Materialized Views with Aggregates

Refresh on commit

物化檢視的重新整理將作為事務的一部分，因而事務提交時間將稍微長一點。

例子

CREATE MATERIALIZED VIEW LOG ON T_fact WITH ROWID, SEQUENCE (
  T_START_DATE,
  Customer_key,
  T_id,
  serve_company,
  SUCCESS_COUNT
)
INCLUDING NEW VALUES;


DROP MATERIALIZED VIEW T_agg;


CREATE MATERIALIZED VIEW T_AGG
PARALLEL 
PARTITION BY RANGE(T_START_DATE) INTERVAL(NumToYMInterval(1, 'MONTH'))
SUBPARTITION BY HASH(Customer_key) SUBPARTITIONS 512 
( PARTITION part_dummy VALUES LESS THAN (to_date('2014/01/01','yyyy/mm/dd')))
BUILD IMMEDIATE
REFRESH FAST ON DEMAND 
ENABLE QUERY REWRITE 
AS
SELECT T_START_DATE, 
       Customer_key,
       T_id,
       serve_company,
       SUM(SUCCESS_COUNT),
       COUNT(SUCCESS_COUNT),
       COUNT(*)
    FROM T_fact
    GROUP BY T_START_DATE,Customer_key,T_id,serve_company;


PL/SQL輔助包

Oracle提供了兩個PL/SQL包
DBMS_MVIEW
DBMS_ADVISOR

dbms_mview.explain_mview函式分析指定MV是否支援Fast Refresh, Query Rewrite，將結果存入mv_capabilities_table.。
該表需要事先執行$ORACLE_HOME/rdbms/admin/utlxmv.sql建立。表中還會給出不支援Fast Refresh, Query Rewrite的原因。

set serveroutput on;
truncate table mv_capabilities_table;
EXEC dbms_mview.refresh('T_AGG');
exec DBMS_MVIEW.EXPLAIN_MVIEW('T_AGG');
select * from mv_capabilities_table;

dbms_advisor.tune_mview：調優MV及其Log的DDL語句，使之能支援Fast Refresh和Query Rewrite。返回一個Task Name，使用者可以根據該Task Name去User_tune_mview中查詢優化後的DDL。

declare
  v_msg varchar2(4000);
begin
dbms_ADVISOR.TUNE_MVIEW(v_msg,
''
    );
    
dbms_output.put_line(v_msg);
end;

Select * from user_tune_mview where task_name='';
最後，執行該表中DDL即可。

為什麼Query Rewrite沒有發生？

我們用一個例子說明，採用Oracle自帶的SH schema，該schema預定義一個sales表和物化檢視CAL_MONTH_SALES_MV。我們做一個很簡單的測試：手工執行物化檢視的select部分。Explain Plan已經能夠感知Query Rewrite，預期的“執行計劃”如下：


這裡Oracle沒有使用物化檢視，即Query Rewrite沒有發生。但是Oracle提供了一個非常有用的工具：dbms_mview.EXPLAIN_REWRITE
DBMS_MVIEW.EXPLAIN_REWRITE (
query VARCHAR2,
mv VARCHAR2(30),
statement_id VARCHAR2(30));

This procedure enables you to learn why a query failed to rewrite, or, if it rewrites, which materialized views will be used. Using the results from the procedure, you can take the appropriate action needed to
make a query rewrite if at all possible. The query specified in the EXPLAIN_REWRITE statement is never actually executed.

在這之前，執行一次$ORACLE_HOME/rdbms/admin/utlxrw.sql在當前schema建立REWRITE_TABLE。
然後我們可以執行這個儲存過程來得知為什麼Query Rewrite沒有發生。
declare
v_sql varchar2(1000) := 'SELECT   t.calendar_month_desc
  ,        sum(s.amount_sold) AS dollars
  FROM     sales s
  ,        times t
  WHERE    s.time_id = t.time_id
  GROUP BY t.calendar_month_desc';

begin
  DBMS_MVIEW.EXPLAIN_REWRITE (v_sql,'SH.CAL_MONTH_SALES_MV', 'TEST1');
end;

select  query, rewritten_txt, message, pass from   REWRITE_TABLE where statement_id='TEST1';

對照著這個結果，我們一個個改正就行了！裡面詳細內容請見下一篇

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1062750/，如需轉載，請註明出處，否則將追究法律責任。

相關文章