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。赋予权限后可创建，一切正常。