Posted on 2006-03-28 16:35
柳隨風(fēng) 閱讀(8719)
評論(3) 編輯 收藏 所屬分類:
oracle開發(fā)應(yīng)用
最近兩天在學(xué)習(xí)oracle物化視圖，學(xué)了兩天，總要輸出一些學(xué)習(xí)心得，寫的比較隨意，本來想整理，但上次整理花了一天時間，目前還要更重要的事情，故先輸出，有時間再整理。
使用場景：
????????????? 在只讀或“精讀”環(huán)境工作更好，不適用高端的聯(lián)機處理系統(tǒng)，在并發(fā)事務(wù)不是很高的系統(tǒng)也可以使用
使用前提：
????????????? 需要調(diào)整初始參數(shù) query_rewrite_enabled，該參數(shù)可以動態(tài)調(diào)整，不需要重啟
?????????????? alter system set query_rewrite_enabled=true;
????????????? 相關(guān)參數(shù)還有query_rewrite_integrity 該參數(shù)值有三個enforced、trusted、stale_tolerated 調(diào)整查詢重寫級別，enforced級別最低，是默認(rèn)值。可重寫查詢的可能最小、（個人理解）
簡單例子：
??????????????? create? materialized view? mview_owner_sum
??????????????? build immediate
??????????????? refresh on commit
??????????????? enable query rewrite
??????????????? as
??????????????? select count(*),owner from test
???????????????? group by owner;
oracle在數(shù)據(jù)更新后有可能（refresh on commit）自動重寫物化視圖，但不是能對任意的物化視圖進(jìn)行同步，對單一表或者沒有聚集的連接可以重寫。
查詢重寫：
????????????? 如果查詢語句符合如下相關(guān)條件，oracle優(yōu)化器會從物化視圖中查詢，也就是查詢重寫。
????????????? 1、sql和定義視圖的sql 完全匹配，可忽略空白字符大小以及其他格式
????????????? 2、部分正文匹配
????????????? 3、查詢的數(shù)據(jù)可以從物化視圖中提取出來
????????????? 4、連接兼容
????????????? 5、分組兼容
????????????? 6、聚集兼容
??????????? （4、5、6的原則實際上就是做相關(guān)連接、分組、聚集相關(guān)數(shù)據(jù)全包含在物化視圖中才能重寫查詢)
????????????? 7、另外在不同的優(yōu)化策略下，是否查詢重寫是不一樣的。在默認(rèn)choose模式數(shù)據(jù)量不大的情況基本不會查詢重寫。
根據(jù)上述原則，可通過如下的方法達(dá)到盡可能的查詢重寫，達(dá)到優(yōu)化的目的：
1、 盡可能的增加物化視圖對應(yīng)的基本表的之間的約束關(guān)系，如主鍵、外鍵，等，
這樣查詢是如果對應(yīng)數(shù)據(jù)可從物化視圖中提取出來，系統(tǒng)會有可能改寫查詢，采用物化視圖
2、可以通過dimension對象可以指定相關(guān)表、字段之間的關(guān)系
應(yīng)用測試：
正好同事有相關(guān)的統(tǒng)計性能問題，用物化視圖嘗試了一把，查詢的速度快了二十倍，但數(shù)據(jù)更新確要16秒，（視圖是采用refresh on commit方式創(chuàng)建），不能解決該問題(數(shù)據(jù)量不大，做完表、索引分析后查詢在0.5秒左右。
遺留問題：
1、創(chuàng)建了維對象，對應(yīng)的執(zhí)行計劃沒有發(fā)生改變，沒有達(dá)到創(chuàng)建維的目的
2、如果是 refresh on?demand 方式創(chuàng)建的如何刷新數(shù)據(jù)。
3、缺乏詳細(xì)的物化視圖創(chuàng)建語法說明，只是達(dá)到基本了解物化視圖的程度
相關(guān)學(xué)習(xí)腳本：
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;
篇外話：
???????????做測試導(dǎo)入的時候，對應(yīng)表空間不存在，原本想通過收回創(chuàng)建無限空間的權(quán)限，
達(dá)到可以導(dǎo)入到用戶對應(yīng)表空間，結(jié)果不行，只好重新創(chuàng)建對應(yīng)表空間導(dǎo)入。后來忘了賦予對應(yīng)用戶權(quán)限，結(jié)果今天創(chuàng)建物化視圖報ora-01536? 超過空間限量,初以為是表空間不夠，實際是對應(yīng)的用戶沒有無限使用表空間的權(quán)限alter user username quota unlimited on tablespacename。賦予權(quán)限后可創(chuàng)建，一切正常。
?????????????