Oracle 物化视图案例分享

一、简介
物化视图顾名思义就是物理化了视图，使它实际存储到了硬盘上，查询性能能比普通的视图好
二、物化视图原理：
1、生成数据
两大项：build immediate   build deferred
Build immediate:在创建物化视图的同时根据主表生成数据
Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh(‘MV_name’,’C’)，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。
2、关于刷新
种刷新方式：complete fast force
Complete :完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新
Fast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创建物化视图日志（物化视图日志记录了数据更新的日志）
Force:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）
不过从实际情况出发，应该尽量不使用默认项，可以考虑使用增量刷新，对大表特别效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结…..）


种刷新时间：on demand on commit start with/ next
On demand:在需要刷新时进行刷新（人工判断）
On commit:在基表上有提交操作时，进行更新
Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）
Next:刷新的周期时间


实验：
实例演示(分两台主机操作，主表主机和物化视图主机)
1、创建表空间 (主表机器操作)
create tablespace tbs_mview datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\mview_tbs.dbf' size 100m autoextend on next 50m;
2、创建用户(主表机器操作）
create user hurp_mv identified by "hurp_mv" default tablespace tbs_mview;
3、给用户赋权（主表机器操作）
grant CREATE MATERIALIZED VIEW  to hurp_mv;
grant connect,resource to hurp_mv;
grant create table to hurp_mv;
4、用户建立测试表(主表机器操作)
create table test(id int, name char(10));
5、插入数据(主表机器操作)
insert into test(id,name) values(1,'a');
insert into test(id,name) values(2,'a');
insert into test(id,name) values(3,'a');
insert into test(id,name) values(4,'a');
insert into test(id,name) values(5,'a');
insert into test(id,name) values(6,'a');
insert into test(id,name) values(7,'a');


6、备机上建用户和表空间
CREATE TABLESPACE tbs_mview DATAFILE '/oradata/shrnc/mview_tbs.dbf'  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
create user hurp_mv identified by "hurp_mv" default tablespace tbs_mview;


grant connect,resource to hurp_mv;
grant create table to hurp_mv;
grant CREATE MATERIALIZED VIEW to hurp_mv;


7、创建DBLINK (物化视图机器操作)
create database link  ora10g connect to hurp_mv identified by "hurp_mv" using 'master';
CREATE DATABASE LINK  to_test
CONNECT TO "sc" identified by "test"
using 'Sc_test';
8、建立视图日志(主表机器操作)
CREATE MATERIALIZED VIEW LOG ON test WITH rowid;
9、创建物化视图 (物化视图机器操作)
create MATERIALIZED VIEW test_view
REFRESH fast
ON demand  with primary key 
start with sysdate next sysdate+1/1440 as select id,name from hurp_mv.test@ora10g;


10、手动刷视图
BEGIN
   DBMS_MVIEW.refresh (
      LIST=> 'test_view',
      METHOD        => 'F',
      PARALLELISM   => 1);
END;
/


注：这里采用的是定时同步，需求可以实时同步


诊断：
begin
dbms_mview.explain_mview('select * from hurp_mv.test@ora10g');
end;
/
select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%';




@?/rdbms/admin/utlxmv.sql






批量插入脚本：
create or replace procedure batch_insert is 
v_sql02 varchar2(3999);
v_value number;
begin
for i in 1..10000 loop 
v_value:=i;
v_sql02:='insert into hurp_mv.test values(:i,''a''||:i)';
execute immediate v_sql02 using v_value,v_value;   
commit;                                            
end loop;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1405826/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29446986/viewspace-1405826/