第六课 2 物化视图

二 物化视图

物化视图：执行sql并保留结果，直接放在数据文件中，不放在内存中方便重用【空间换时间】

场合：同步数据，过滤数据并两次利用

1.要求创建跨库物化视图并可手动更新

官方文档：PL/SQL Packages and Types Reference -> 61 DBMS_MVIEW

基表和物化视图不在同一个库上，基表和日志在LEO2上，物化视图在LEO1上，在LEO1上可手动刷新物化视图

JCH112

create user mv_jch2 identified by mv_jch2;

grant connect,resource to mv_jch2;

conn mv_jch2/mv_jch2

create table t (a varchar2(10));

insert into t values ('hmilyjch');

commit;

select * from t;

创建物化视图日志，基表在哪个库，日志就在哪个库，用于记录基表所有变化，实时刷新物化视图，完全刷新不需要物化视图日志，快速刷新需要物化视图日志

create materialized view log on t with rowid;    --基于rowid变化记录到日志中

MV_JCH2@JCH2> create materialized view log on t with rowid;

Materialized view log created.

MV_JCH2@JCH2>

JCH111

创建dblink

sqlplus / as sysdba@JCH111

create public database link dblink_to_jch112 connect to mv_jch2 identified by mv_jch2 using 'JCH112';

注：global_names参数为FALSE 才允许DB link名字与我远程连接实例名不一致

SQL> show parameter global_names;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

global_names boolean FALSE

SQL>

create user mv_jch1 identified by mv_jch1;

grant dba to mv_jch1;

conn mv_jch1/mv_jch1

创建跨库可刷新物化视图

create materialized view mv_t refresh fast with rowid as select * from t@dblink_to_jch112;

SQL> create materialized view mv_t refresh fast with rowid as select * from t@dblink_to_jch112;

Materialized view created.

SQL> select * from mv_t;

A

----------

hmilyjch

SQL>

此时数据跟基表一致，在登录到JCH112上面，插入一条数据

sqlplus mv_jch2/mv_jch2@JCH112

insert into t values ('hmilyjch2');

commit;

select * from t;

--此时t表有2条数据

sqlplus mv_jch1/mv_jch1@JCH111

select * from mv_t;   --发现物化视图没有更新！

快速刷新方法：增量刷新 或 完全刷新方法：全表刷新

exec dbms_mview.refresh('mv_t','f'); 手动刷新物化视图  增量酸辛

exec dbms_mview.refresh('mv_t','c');

select * from mv_t;

2.要求创建物化视图具有自动快速刷新功能

提交即刷新。

sqlplus mv_jch1/mv_jch1@JCH111

注：grant connect,resource,create materialized view to mv_jch1; --如果是普通用户需要授予创建物化视图权限

drop table t1 purge;

create table t1 (x int,y int,z int);

insert into t1 values (1,2,3);

insert into t1 values (4,5,6);

commit;

select * from t1;

create materialized view log on t1 with sequence,rowid (x,y,z) including new values;

我们只看x和y列信息

drop materialized view mv_t1;

create materialized view mv_t1 build immediate refresh with rowid fast on commit enable query rewrite as select x,y from t1;

参数说明：

build immediate：创建物化视图时，立即刷新基表

fast on commit：支持基于commit动作的自动刷新

enable query rewrite：启动查询重写功能 ，比较物化视图的执行计划，和基表的执行计划，哪个执行计划比较好，就是用哪个。

refresh with rowid：物化视图默认是基于主键PK方式来刷新的，由于基表没有主键，因此我们基于ROWID刷新

select * from mv_t1;

验证物化视图是否随记录增加而更新

insert into t1 values (7,8,9);

select * from t1;

select * from mv_t1; mv_t1随记录增加而木有刷新，必须commit之后才触发物化视图刷新

commit;

select * from mv_t1;

delete from t1 where x=1;

select * from t1;

commit;

select * from mv_t1;