Oracle物化视图原理实例解析

最近项目上需要数据库表优化，看了对大数据量的表进行在线重定义进行分区，偶然看到物化视图的概念，感觉也十分有用，这篇文章写的能看懂故转载 出处 http://leonarding.blog.51cto.com/6045525/1354990

场合：数据变化小，查询出数据还要2次利用，需要数据双向同步的场合
视图：就是一条sql语句，每次查询时都要重新生成执行计划，重新执行，非常消耗时间，放在内存中一次性的
物化视图：执行sql并保留结果，直接放在数据文件中，不放在内存中方便重用【空间换时间】，不受开关机的影响
1.创建基表并插入数据
create table sino_person_address
(
iid NUMBER(16) not null,
ipersonid NUMBER(16),
spin NUMBER(16),
dgettime DATE,
sorgcode VARCHAR2(20),
smsgfilename VARCHAR2(20),
ilineno NUMBER(8),
saddress VARCHAR2(60),
szip CHAR(6),
scondition CHAR(1),
itrust NUMBER(1),
stoporgcode VARCHAR2(14),
istate NUMBER(1),
constraint PK_SINO_PERSON_ADDRESS primary key (iid)
);
插入数据(插入自动增长***字段的方法)
INSERT 语句插入这个字段值为: ***的名称.NEXTVAL,seq_sino_person_address.nextval
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-08 12:12:12','yyyy-mm-dd hh24:mi:ss'),'110','test_report',111,'beijing
xicheng','100100','1',123,1,'1000',0);
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-09 12:12:12','yyyy-mm-dd hh24:mi:ss'),'120','test_report2',121,'beijing
xicheng','100200','2',123,1,'1002',2);
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-10 12:12:12','yyyy-mm-dd hh24:mi:ss'),'130','test_report3',131,'beijing
xicheng','100300','3',123,1,'1003',3);
commit
###################################################################################################
2.创建物化视图日志
意义：记录基表DML操作的变化，实时刷新物化视图
注：包含所有字段
删除物化视图日志
drop materialized view log on t
创建物化视图日志
create materialized view log on t with sequence,rowid (x,y,z) including new values;
参数说明：
with sequence：以序号增1的方式进行变化记录
rowid (x,y,z)：定位哪些数据发生了变化，日志记录rowid指向的数据块的位置和变化
删除物化视图日志
drop materialized view log on sino_person_address;
基于主键方式的刷新，创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON sino_person_address
WITH PRIMARY KEY
INCLUDING NEW VALUES
【TABLESPACE sinojfs2】; 可选项
3.创建物化视图
创建物化视图
create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;
删除物化视图
drop materialized view mv_sino_person_address;
create materialized view mv_sino_person_address
tablespace SINOJFS2
build immediate 创建物化视图时，立即刷新基表
refresh fast with primary key 支持基于主键的快速刷新（增量刷新），基表必须有主键
on commit 支持commit动作自动刷新
enable query rewrite
as select * from sino_person_address;
create materialized view mv_sino_person_address
tablespace SINOJFS2
build immediate
refresh fast with primary key refresh complete全部刷新【全表刷新】可选项
on demand 支持需求时手工刷新
enable query rewrite
as select * from sino_person_address;
########################################################################################
参数说明：
build immediate：创建物化视图时，立即刷新基表
refresh fast with primary key：支持基于主键的快速刷新（增量刷新），基表必须有主键
on commit：基于commit动作的自动刷新 on demand：基于需求时的手工刷新
enable query rewrite：支持查询重新（使用物化视图代替基表，查询必须重写，查询重写是透明的并且不需要对物化视图有任何权限，物化视图可以启用和禁用查询重写）
查询重写：select * from t基表，执行计划走的是mv_t物化视图，禁用后，执行计划走的就是t基表了
tablespace SINOJFS2 创建于SINOJFS2表空间
(1)创建方式：BUILD IMMEDIATE（立即生成数据）， BUILD DEFERRED（下一次刷新时生新数据）， ON PREBUILD TABLE（不创建新的数据段，用已存在的含有当前物化视图数据的表来代替）；   
(2)ENABLE | DISABLE QUERY REWRITE指定是否启用当前物化视图用于查询重写，启用该选项时，系统会检查以保证查询的可确定性（不允许有如序列数，USER， DATE等不确定的返回值），DISABLE时物化视图照样可以被刷新；    
与物化视图生效相关的设置    
(1)初始化参数JOB_QUEUE_PROCESSES设置大于零，物化的自动刷新操作需要JOB QUEUE进程来执行；    
(2)初始化参数OPTIMIZER_MODE要设成某种CBO优化模式；    
(3)用户会话有QUERY_REWRITE（优化器能将查询重写到本方案物化视图）或GLOBAL_QUERY_REWRITE（优化器能将查询重写到其它方案的物化视图）系统权限；    
(4)初始化参数QUERY_REWRITE_ENABLED 指示优化器是否动态重写查询来使用物化视图，这个参数可以在四个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION， HINTS）；    
(5)初始化参数QUERY_REWRITE_INTEGRITY 指示优化器在不同的数据一致性情况下决定是否使用物化视图来重写查询，ENFORCED（只有在能确保数据一致的前提下才使用物化视图）， TRUSTED（数据不一定一致，只要有用维度对象定义的关系存在，就可使用物化视图）， STALE_TOLERATED（数据不一致，也没有相关的维度定义时仍可使用物化视图），这个参数可以在三个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION）；
4. 物化视图DML操作测试
（1）验证物化视图是否随记录增加而增加
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-11 13:13:13','yyyy-mm-dd hh24:mi:ss'),'140','test_report4',141,'beijing
xicheng','100400','4',123,1,'1004',4);
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录增加而木有刷新，必须commit之后才触发物化视图刷新，没有问题
exec dbms_mview.refresh('mv_sino_person_address','c'); 还可以手动全部刷新【全表刷新】（先清除，再重装数据）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（2）验证物化视图是否随记录删除而减少
delete from sino_person_address where iid=21;
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录删除而木有刷新，必须commit之后才触发物化视图刷新，没有问题
exec dbms_mview.refresh('mv_sino_person_address','c'); 还可以手动全部刷新【全表刷新】（先清除，再重装数据）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（3）验证物化视图是否随记录修改而更新
update sino_person_address set sorgcode='200' where sorgcode='120';
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录修改而木有刷新，必须commit之后才触发物化视图刷新，没有问题
exec dbms_mview.refresh('mv_sino_person_address','c'); 还可以手动全部刷新【全表刷新】（先清除，再重装数据）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（4）验证物化视图是否随truncate而清空
truncate table sino_person_address;
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录truncate而木有清空，必须手动truncate table mv_sino_person_address;才能清空（两者是没有关联的），没有问题
5.物化视图刷新
根据业务需求，每月定时刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过程，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。
定义存储过程
create or replace procedure pro_mview_refresh
as
begin
dbms_mview.refresh('mv_sino_person_address','f');
end;
/
执行存储过程
execute pro_mview_refresh;
还可以刷新所有物化视图 dbms_mview.refresh_all_mviews;
创建存储过程
drop procedure pro_refresh_all_mviews;
create or replace procedure pro_refresh_all_mviews
as
i number;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
执行
executepro_refresh_all_mviews;
set serveroutput on;不可放在存储过程中，因为这是sqlplus命令，如果你怕忘记或者嫌麻烦可以把set serveroutput on;
写入/opt/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql中，每次使用sqlplus时自动加载这个文件
如果想用PL/SQL Developer工具访问数据库，请在C:\Program Files\PLSQL Developer\Login.sql 文件里添加
-- Autostart Command Window script
set serveroutput on;
这样以后再使用PL/SQL Developer工具访问数据库就可以自动加载这条命令了
###############################################################################################
研发人员专用，手动刷新，想刷就刷
set serveroutput on; 打开屏幕显示功能，就可以看到number_of_failures=>0结果
PL/SQL 匿名块
declare
i number;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
number_of_failures=>0
Number_of_failures 表示刷新物化视图失败个数
采用默认refresh force 刷新方式：先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式
注意:
1、 如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新，从不刷新）。
NEVER REFRESH（不刷新）
REFREST FAST（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
REFRESH COMPLETE（先清除，再重装数据）
REFRESH FORCE（先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式）
确定刷新时机：
ON COMMIT（事务提交时刷新），
ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS来手工刷新），
By Time（用START WITH 和 NEXT 子句创建的job来定时自动刷新）；
[dbms_mview.refresh('mv_sino_person_address,mv_person_address_his','ff');]
2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量刷新。
drop materialized view log on sino_person_address; 删除日志
SQL> exec dbms_mview.refresh('mv_sino_person_address','c'); 删除物化视图日志，只可以支持物化视图全部刷新
PL/SQL procedure successfully completed
#################################################################################
SQL> exec dbms_mview.refresh('mv_sino_person_address','f'); 无法增量刷新
begin dbms_mview.refresh('mv_sino_person_address','f'); end;
ORA-23413: 表 "SINOJFS"."SINO_PERSON_ADDRESS" 没有实体化视图日志
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 2
SQL> create materialized view log on sino_person_employment 重新创建物化视图日志
2 with primary key
3 including new values;
Materialized view log created
SQL> exec dbms_mview.refresh('mv_sino_person_employment','f'); 但还是不支持增量刷新，因为日志内容和原表内容不一致了
begin dbms_mview.refresh('mv_sino_person_employment','f'); end;
ORA-12034: "SINOJFS"."SINO_PERSON_EMPLOYMENT" 上的实体化视图日志比上次刷新后的内容新
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 2
因为：丢失了删除日志那一点->重建日志那一点之间的原表DML变化，因此日志内容和原表内容不一致了
解决方案：重建物化视图日志 重新【增量刷新】和【全表刷新】一遍
SQL> drop materialized view log on sino_loan_compact; 删除日志
Materialized view log dropped
SQL> create materialized view log on sino_loan_compact 重建日志
2 with primary key
3 including new values;
Materialized view log created
SQL> exec dbms_mview.refresh('mv_sino_loan_compact','c'); 必须先全表刷新
PL/SQL procedure successfully completed
SQL> exec dbms_mview.refresh('mv_sino_loan_compact','f'); 再增量刷新，否则ORA-12034: "SINOJFS"."SINO_LOAN_COMPACT" 上的实体化视图日志比上次刷新后的内容新
PL/SQL procedure successfully completed
小结：只要能够增量刷新，说明日志没有问题了
简述所有视图的快速刷新和全表刷新命令（测试使用） 10张视图
select owner,table_name,tablespace_name,status from dba_tables where table_name in ('SINO_LOAN_APPLY');
update SINO_LOAN_APPLY set sorgcode ='1000' where iid =858;
exec dbms_mview.refresh('mv_sino_loan_compact','c');
exec dbms_mview.refresh('mv_sino_loan_compact','f');
exec dbms_mview.refresh('mv_sino_loan_apply','c');
exec dbms_mview.refresh('mv_sino_loan_apply','f');
exec dbms_mview.refresh('mv_sino_loan_spec_trade','c');
exec dbms_mview.refresh('mv_sino_loan_spec_trade','f');
exec dbms_mview.refresh('mv_sino_loan','c');
exec dbms_mview.refresh('mv_sino_loan','f');
exec dbms_mview.refresh('mv_sino_loan_guarantee','c');
exec dbms_mview.refresh('mv_sino_loan_guarantee','f');
exec dbms_mview.refresh('mv_sino_loan_investor','c');
exec dbms_mview.refresh('mv_sino_loan_investor','f');
###############################################################################
exec dbms_mview.refresh('mv_sino_person_employment','c');
exec dbms_mview.refresh('mv_sino_person_employment','f');
exec dbms_mview.refresh('mv_sino_person_address','c');
exec dbms_mview.refresh('mv_sino_person_address','f');
exec dbms_mview.refresh('mv_sino_person_certification','c');
exec dbms_mview.refresh('mv_sino_person_certification','f');
exec dbms_mview.refresh('mv_sino_person','c');
exec dbms_mview.refresh('mv_sino_person','f');
3.基表增加字段后对应物化视图不能自动同步结构
业务表增加 上报状态 字段 ipbcstate number(1) 可以为空
文档 建模 脚本 物化视图
sino_person_certification 完成 完成 完成 完成
sino_person 完成 完成 完成 完成
sino_person_address 完成 完成 完成 完成
sino_person_employment 完成 完成 完成 完成
sino_person_address_his 完成 完成 完成
sino_person_employment_his 完成 完成 完成
sino_person_his 完成 完成 完成
sino_loan 完成 完成 完成 完成
sino_loan_compact 完成 完成 完成 完成
sino_loan_spec_trade 完成 完成 完成 完成
sino_loan_guarantee 完成 完成 完成 完成
sino_loan_investor 完成 完成 完成 完成
sino_loan_apply 完成 完成 完成 完成
对比IPBCSTATE 字段基表有，但物化视图没有，需要重建物化视图解决
select * from mv_sino_loan_compact where rownum<2;
select * from sino_loan_compact where rownum<2;
select * from mv_sino_loan where rownum < 2;
select * from sino_loan where rownum < 2;
select * from mv_sino_loan_apply where rownum < 2;
select * from sino_loan_apply where rownum < 2;
select * from mv_sino_loan_guarantee where rownum < 2;
select * from sino_loan_guarantee where rownum < 2;
select * from mv_sino_loan_guarantee where rownum < 2;
select * from sino_loan_guarantee where rownum < 2;
select * from mv_sino_loan_investor where rownum < 2;
select * from sino_loan_investor where rownum < 2;
select * from mv_sino_loan_spec_trade where rownum < 2;
select * from sino_loan_spec_trade where rownum < 2;
################################################################################
select * from mv_sino_person where rownum < 2;
select * from sino_person where rownum < 2;
select * from mv_sino_person_address where rownum < 2;
select * from sino_person_address where rownum < 2;
select * from mv_sino_person_certification where rownum < 2;
select * from sino_person_certification where rownum < 2;
select * from mv_sino_person_employment where rownum < 2;
select * from sino_person_employment where rownum < 2;
##################################################################################
4.因为上面写的物化视图是基于主键进行刷新的，因此原表必须要有主键
6.定时刷新JOB
确定执行时间间隔   
1)、 每分钟执行    
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)    
2)、 每天定时执行    
例如：每天下午2点执行一次pro_mview_refresh存储过程    
Interval => TRUNC(sysdate) + 1 +14/ (24)    
3)、 每周定时执行    
例如：每周一凌晨2点执行    
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天    
4)、 每月定时执行    
例如：每月1日凌晨2点执行    
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24    
5)、 每季度定时执行    
例如每季度的第一天凌晨2点执行    
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24    
6)、 每半年定时执行    
例如：每年7月1日和1月1日凌晨2点    
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24    
7)、 每年定时执行    
例如：每年1月1日凌晨2点执行    
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
通过jobs的使用就能实现每天或每月的指定时间执行一个函数、过程与命令
set serveroutput on 启动屏幕输出功能
SQL> execute dbms_output.put_line('This is'); 已经可以正常输出
This is
创建作业
variable job_num number; 定义存储job编号的变量
declare job_num number; pro_refresh_all_mviews
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'sysdate+1/1440'); 每天1440分钟，每一分钟运行pro_mview_refresh过程一次
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
############################################################################################
绑定变量版，必须先定义变量
variable job_num number;
declare job_num number;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE+5/1440,''MI'')'); 每5分钟运行一次job
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
例如：每天上午10点执行一次pro_refresh_all_mviews存储过程   
Interval => TRUNC(sysdate) + 1 +10/ (24)    
declare job_num number;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24'); 每天上午10点运行一次job
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
Job Number is
PL/SQL procedure successfully completed
job_num
---------
1
####################################################################################
PL/SQL 匿名块版，可以直接在块中定义变量，比较方面现在采用这种
declare
job_num number;
begin
dbms_job.submit
(job=>job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24');
dbms_output.put_line('Job Number is '||job_num);
commit;
end;
/
Job Number is 4
PL/SQL procedure successfully completed
####################################################################################
dbms_job.submit( job out binary_integer,   
what in varchar2,    
next_date in date，    
interval in varchar2,    
no_parse in boolean)
●job：输出变量，这是作业在作业队列中的编号；   
●what：执行作业的存储过程及其输入参数；    
●next_date：作业初次执行的时间；    
●interval：作业执行的时间间隔。指上一次执行结束到下一次开始执行的时间间隔    
其中Interval这个值是决定Job何时，被重新执行的关键；当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。如何更好地确定执行时间的间隔需要我们掌握一个函数TRUNC。
SQL> show parameter job_queue_process 作业队列进程数，oracle能够并发job数量，0~1000
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
Oracle提供的数据字典user_jobs监控作业状态
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;
Job 作业唯一编号
Log_user 提交作业的用户
What 作业执行的存储过程
Last_date 最后一次成功运行作业的日期
Last_sec 最后一次成功运行作业的时间
Next_date 下一次运行作业日期
Next_sec 下一次运行作业时间
Failures 执行失败次数，当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1，加到16之后Oracle就不在执行它了
Broken 是否是异常作业，当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken，或强制调用dbms_job.run来重新执行它。Y标示作业中断，以后不会运行，N表示作业正常，可以运行
运行作业   
begin    
         dbms_job.run(:job_num); job_num是存储job编号的变量    
end;    
查询作业状态    
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;
JOB LOG_USER WHAT LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- --------- ---------------------------------------------------- ----------- ---------------- -----------
1 SINOJFS pro_refresh_all_mviews; 2013-4-26 1 11:27:38 2013-4-27 1 10:00:00 0 N
Job 作业唯一编号
Log_user 提交作业的用户
What 作业执行的存储过程
Last_date 最后一次成功运行作业的日期
Last_sec 最后一次成功运行作业的时间
Next_date 下一次运行作业日期
Next_sec 下一次运行作业时间
Failures 执行失败次数，当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1，加到16之后Oracle就不在执行它了
Broken 是否是异常作业，当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken；
或强制调用dbms_job.run来重新执行它。Y标示作业中断，以后不会运行，N表示作业正常，可以运行
删除作业   
begin    
         dbms_job.remove(:job_num);    
end;
修改作业
dbms_job.remove(jobno); 删除job号
例 execute dbms_job.remove(1);
######################################################################
dbms_job.what(jobno,what); 修改执行的存储过程
dbms_job.next_date(job,next_date)修改下次执行的时间
例 exec dbms_job.next_date(46,sysdate+2/(24*60)); 46作业号
#####################################################################
dbms_job.interval(job,interval)   :修改间隔时间
例 exec dbms_job.interval(46,sysdate+3/(24*60));
######################################################################   
dbms_job.broken(job,true)     中断job
例 exec dbms_job.broken(46,true); 46作业号 exec dbms_job.broken(2,true) BROKEN=Y
#######################################################################
dbms_job.broken(job,false,next_date)   next_date:下次执行时间，如果不填则马上启动job
例 exec dbms_job.broken(46,false); 启动job exec dbms_job.broken(2,false); BROKEN=N
########################################################################
dbms_job.run(jobno); 运行作业
例子 execute dbms_job.run(1);