物化视图学习笔记一

add by htliu  -2009/10

基本说明
create table t(key int primary key,val varchar2(50));
插入记录
select *  from t
1 1 a
2 2 b
3 3 c

----------
create view v as select * from t;

select  * from v;此时普通视图和表一样查询

-------------
select rowid,a.* from t a;
1 AAAMmTAAHAAAAAuAAA 1 a
2 AAAMmTAAHAAAAAuAAB 2 b
3 AAAMmTAAHAAAAAuAAC 3 c
select rowid,a.* from v a;
1 AAAMmTAAHAAAAAuAAA 1 a
2 AAAMmTAAHAAAAAuAAB 2 b
3 AAAMmTAAHAAAAAuAAC 3 c
可以看出视图查询数据是完全根据基表来的
----------------------------------------
创建物化视图：

create materialized view mv as select *  from  t;

select rowid,m.* from mv m;
1 AAAMmWAAHAAAAA8AAA 1 a
2 AAAMmWAAHAAAAA8AAB 2 b
3 AAAMmWAAHAAAAA8AAC 3 c-----可以看出rowid发生变化  也即不是原来记录

---------------
update t set val='aa' where key=1;
commit
select * from t=select * from v;
1 1 aa
2 2 b
3 3 c
select * from mv;---没有变化  可以从以上看出物化视图创建时就是一个基表的简单拷贝
1 1 a
2 2 b
3 3 c
-----下面研究刷新
刷新类型：完全、增量、（还有force是能全则全否则增量）
模式：手动、自动

---手动刷新
SQL> exec dbms_mview.refresh('mv','c');
 
PL/SQL procedure successfully completed
 
查询：select rowid,b.* from mv b;  ---数据已经更新
1 AAAMmWAAHAAAAA8AAD 1 aa
2 AAAMmWAAHAAAAA8AAE 2 b
3 AAAMmWAAHAAAAA8AAF 3 c---可以看出rowid也变化了  其实在刷新的时候做了删除再插入操作

---常用刷新参数
dbms_mview.refresh(’CUST_SALES’, parallelism => 10);---数据大的时候并行度
dbms_mview.refresh_dependent (’SALES’);---基于该表的所有mv都刷新
dbms_mview.refresh_all_mviews;---所有mv都刷新

------------------fast研究-------------------------------------------------------------------------

drop  materialized view mv;

create materialized view log on t;

select *   from user_objects;查看生成MLOG$_T表跟踪基表变化，并且一个基表只能创建一个mlog$

create materialized view mv refresh fast as select * from t;--创建fast mv

select aa.rowid,aa.* from mv aa

1 AAAMmbAAHAAAABEAAA 1 aa
2 AAAMmbAAHAAAABEAAB 2 b
3 AAAMmbAAHAAAABEAAC 3 c

update t set val='aaa' where key=1;
commit;
select * from MLOG$_T
1 1 4000-1-1 U U 04

exec dbms_mview.refresh('mv','f');
select count(*) from MLOG$_T;---=0 同步后自动清空，

select aa.rowid,aa.* from mv aa

1 AAAMmbAAHAAAABEAAA 1 aa----rowid没变
2 AAAMmbAAHAAAABEAAB 2 b
3 AAAMmbAAHAAAABEAAC 3 c

-----------------------------------------mv 常用法--------------------------------------------

1、优化---大表过滤后产生少量记录
2、dblink+mv数据复制同步

--------------------Query Rewrites-研究----------
--创建测试表
create table my_all_objects
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects;
select count(*)from my_all_objects---147609
insert/*+append*/ into my_all_objects select *  from all_objects;
commit
select count(*)from my_all_objects---344421

analyze table my_all_objects compute statistics;

select owner,count(*) from my_all_objects group by owner;---查询原表my_all_objects消耗很多资源
此时cost1678

--启用re
alter session set query_rewrite_enabled=true;

alter session set query_rewrite_integrity=enforced;

create materialized view  my_all_objects_aggs
build immediate
refresh on commit------------------------------------------自动刷新 当基表变的时候
enable query rewrite
as select owner,count(*) from my_all_objects group by owner;

analyze table my_all_objects_aggs compute statistics;

-------------------------
select owner,count(*) from my_all_objects group by owner;---执行计划变成查询my_all_objects_aggs
此时cost变成很少3

insert into  my_all_objects(owner,object_name,object_type,object_id)values('a','b','c','111111');

select owner,count(*) from my_all_objects where owner='a' group by owner;---数据自动更新 ，并且也是查询my_all_objects_aggs

re会智能判断，否则查询基表
– Ignore alphabetic case
– Recognize equivalent joins
– Compare the defining text of a named view

是否使用了re  可以查看执行计划 前提是cost

* Initialization parameters:
– OPTIMIZER_MODE
– QUERY_REWRITE_ENABLED
– QUERY_REWRITE_INTEGRITY
* Dynamic and session-level param
– QUERY_REWRITE_ENABLED
– QUERY_REWRITE_INTEGRITY
* Hints: REWRITE and NOREWRITE
* Dimensions

SQL> SELECT /*+ NOREWRITE */
2    p.prod_name, SUM (s.quantity_sold),
3    SUM (s.amount_sold)
4  FROM   sales s, products p
5  WHERE s.prod_id = p.prod_id
6  GROUP BY p.prod_name;