Posted on 2006-03-28 16:35
柳随风 阅读(8695)
评论(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。赋予权限后可创建，一切正常。