【原创】《Oracle物化视图实战手册》-原理讲解-应用场景-实战演练

《Oracle物化视图实战手册》场合：数据变化小，查询出数据还要2次利用，需要数据双向同步的场合视图：就是一条sql语句，每次查询时都要重新生成执行计划，重新执

《Oracle物化视图实战手册》

场合：数据变化小，查询出数据还要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之后才触发物化视图刷新，没有问题

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;

/

执行

;

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');

###############################################################################

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;

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;

快速刷新 全部刷新 整体刷新 基表

Leonarding

2013.05.06

北京&autumn

分享技术~成就梦想

Blog：

本文出自 “刘盛分享技术~成就梦想” 博客，请务必保留此出处