Posted on 2006-03-28 16:35
柳隨風 閱讀(8714)
評論(3) 編輯 收藏 所屬分類:
oracle開發應用
最近兩天在學習oracle物化視圖，學了兩天，總要輸出一些學習心得，寫的比較隨意，本來想整理，但上次整理花了一天時間，目前還要更重要的事情，故先輸出，有時間再整理。
使用場景：
????????????? 在只讀或“精讀”環境工作更好，不適用高端的聯機處理系統，在并發事務不是很高的系統也可以使用
使用前提：
????????????? 需要調整初始參數 query_rewrite_enabled，該參數可以動態調整，不需要重啟
?????????????? alter system set query_rewrite_enabled=true;
????????????? 相關參數還有query_rewrite_integrity 該參數值有三個enforced、trusted、stale_tolerated 調整查詢重寫級別，enforced級別最低，是默認值。可重寫查詢的可能最小、（個人理解）
簡單例子：
??????????????? create? materialized view? mview_owner_sum
??????????????? build immediate
??????????????? refresh on commit
??????????????? enable query rewrite
??????????????? as
??????????????? select count(*),owner from test
???????????????? group by owner;
oracle在數據更新后有可能（refresh on commit）自動重寫物化視圖，但不是能對任意的物化視圖進行同步，對單一表或者沒有聚集的連接可以重寫。
查詢重寫：
????????????? 如果查詢語句符合如下相關條件，oracle優化器會從物化視圖中查詢，也就是查詢重寫。
????????????? 1、sql和定義視圖的sql 完全匹配，可忽略空白字符大小以及其他格式
????????????? 2、部分正文匹配
????????????? 3、查詢的數據可以從物化視圖中提取出來
????????????? 4、連接兼容
????????????? 5、分組兼容
????????????? 6、聚集兼容
??????????? （4、5、6的原則實際上就是做相關連接、分組、聚集相關數據全包含在物化視圖中才能重寫查詢)
????????????? 7、另外在不同的優化策略下，是否查詢重寫是不一樣的。在默認choose模式數據量不大的情況基本不會查詢重寫。
根據上述原則，可通過如下的方法達到盡可能的查詢重寫，達到優化的目的：
1、 盡可能的增加物化視圖對應的基本表的之間的約束關系，如主鍵、外鍵，等，
這樣查詢是如果對應數據可從物化視圖中提取出來，系統會有可能改寫查詢，采用物化視圖
2、可以通過dimension對象可以指定相關表、字段之間的關系
應用測試：
正好同事有相關的統計性能問題，用物化視圖嘗試了一把，查詢的速度快了二十倍，但數據更新確要16秒，（視圖是采用refresh on commit方式創建），不能解決該問題(數據量不大，做完表、索引分析后查詢在0.5秒左右。
遺留問題：
1、創建了維對象，對應的執行計劃沒有發生改變，沒有達到創建維的目的
2、如果是 refresh on?demand 方式創建的如何刷新數據。
3、缺乏詳細的物化視圖創建語法說明，只是達到基本了解物化視圖的程度
相關學習腳本：
create materialized view mview_deptsum
build immediate
refresh on demand
enable query rewrite
as select a.deptno,a.dname,count(b.empno)
from dept a,emp b where a.deptno=b.deptno
group by a.deptno,a.dname
?
create table sales(trans_date date,cust_id int,sales_smount number);
insert /*+append */ into? sales
select trunc(sysdate,'year')+mod(rownum,366) trans_date,
mod(rownum,100) cust_id,
abs(dbms_random.random)/100 sales_smount from all_objects;
begin
for i in 1..4 loop
insert /*+append */ into? sales
select? trans_date,
cust_id,
abs(dbms_random.random)/100 sales_smount from sales;
commit;
end loop;
end;
?
create table time_hierarchy
(day primary key,mmyyyy,mon_yyyy,qtr_yyyy,yyyy)
organization index
as
select distinct trans_date day,
cast(to_char(trans_date,'mmyyyy')as number) mmyyyy,
to_char(trans_date,'mon-yyyy') mon_yyyy,
'Q'||ceil(to_char(trans_date,'mm')/3)||'FY'
||to_char(trans_date,'yyyy') QTR_YYYY,
cast(to_char(trans_date,'yyyy') as number)yyyy
from sales;
/
?
create materialized view mv_sales
build immediate
refresh on demand
enable query rewrite
as
select sales.cust_id,sum(sales.sales_smount),time_hierarchy.mm_yyyy
from sales,time_hierarchy
where sales.trans_date =time_hierarchy.day
group by sales.cust_id, time_hierarchy.mm_yyyy
/
select sum(sales.sales_smount),time_hierarchy.mmyyyy
from sales,time_hierarchy
where sales.trans_date =time_hierarchy.day
group by? time_hierarchy.mmyyyy
select sum(sales.sales_smount),time_hierarchy.qtr_yyyy
from sales,time_hierarchy
where sales.trans_date =time_hierarchy.day
group by? time_hierarchy.qtr_yyyy
?
create dimension time_hierarchy_dim
level day is? time_hierarchy.day
level mmyyyy? is time_hierarchy.mmyyyy
level qtr_yyyy? is time_hierarchy.qtr_yyyy
level yyyy????? is time_hierarchy.yyyy
hierarchy time_rollup
(
day child of
mmyyyy child of
qtr_yyyy child of
yyyy
)
attribute mmyyyy
determines mon_yyyy;
篇外話：
???????????做測試導入的時候，對應表空間不存在，原本想通過收回創建無限空間的權限，
達到可以導入到用戶對應表空間，結果不行，只好重新創建對應表空間導入。后來忘了賦予對應用戶權限，結果今天創建物化視圖報ora-01536? 超過空間限量,初以為是表空間不夠，實際是對應的用戶沒有無限使用表空間的權限alter user username quota unlimited on tablespacename。賦予權限后可創建，一切正常。
?????????????