一、前言:
在Oracle文档中,物化视图作为数据仓库的一个技术,但是实际上在OLTP系统中,用来进行数据同步,使用查询重写优化SQL语句都是相当方便的。
二、物化视图创建
1、权限
物化视图中存在三种角色:
分别为:创建者、刷新者、所有者
一般建议这三种角色都为所有者,即你要创建到的schema用户
1)创建者需要的权限:
如果是所有者:CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW
CREATE TABLE or CREATE ANY TABLE
源表和源表的物化视图日志的select权限(如果是远程调用,则赋权限给dblink的user)
如果不是所有者
创建者:CREATE ANY MATERIALIZED VIEW
所有者:CREATE TABLE or CREATE ANY TABLE
源表和源表的物化视图日志的select权限(如果是远程调用,则赋权限给dblink的user)
2)刷新者是所有者
源表和源表的物化视图日志的select权限(如果是远程调用,则赋权限给dblink的user)
刷新者不是所有者
刷新者:alter any materialized view
所有者: 源表和源表的物化视图日志的select权限(如果是远程调用,则赋权限给dblink的user)
三、物化视图创建
物化视图支持的刷新方式:complete、fast、force,分别为全量刷新、增量刷新、默认增量刷新(如果不成功则全量刷新)
增量刷新必须创建源表的物化视图日志
下面重点介绍增量刷新:
1)创建物化视图日志
SQL> create table big2 as select * from dba_objects;
Table created.
SQL> create materialized view log on big2 ;
create materialized view log on big2
*
ERROR at line 1:
ORA-12014: table 'BIG2' does not contain a primary key constraint
说明:物化视图日志创建有二种方式,with [primary key|rowid]默认是第一种。
所以如果主表没有主键的话,选择rowid比较合适
SQL> create materialized view log on big2 with rowid;
Materialized view log created.
2)创建物化视图
SQL> create materialized view big2_mv
2 build immediate
3 refresh force
4 with rowid
5 as select * from big2;
Materialized view created.
创建方式:build immediatedeferred 默认是build immediate
刷新方法:refresh forcecompletefastnever on demandcommit 默认是refresh force demand
日志方式:with primary keyrowid 默认是primary key
四、物化视图刷新
因为创建的时候指定了on demand,刷新就需要人工控制,无非是命令行方式调用刷新或者通过job实现。
1)手工刷新
SQL> insert into big2 select * from dba_objects where rownum<101;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
100
SQL> begin
2 dbms_mview.refresh('big2_mv','fast');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select count(*) from big2_mv;
COUNT(*)
----------
57891
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
0
刷新完以后会自动清除物化视图日志
2)使用刷新组管理刷新
SQL> begin
2 dbms_refresh.make(
3 name => 'ref_grp',
4 list => 'big2_mv',
5 next_date =>sysdate,
6 interval =>'sysdate + 1/24/60/6');
7 end;
8 /
加入刷新组,每10秒进行一次刷新
PL/SQL procedure successfully completed.
SQL> select name,rname from user_refresh_children;
NAME RNAME
------------------------------ ------------------------------
REF_MAIN MV_GRP
BIG_MV2 MV_GRP
BIG2_MV REF_GRP
SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
SQL> select count(*) from big2;
COUNT(*)
----------
57891
SQL> delete from big2;
57891 rows deleted.
SQL> commit;
Commit complete.
SQL> select mview_name,refresh_method,last_refresh_type,last_refresh_date
2 from user_mviews where mview_name='BIG2_MV';
MVIEW_NAME REFRESH_METHOD LAST_REF LAST_REFRESH_D
------------------------------ -------- -------- --------------
BIG2_MV FORCE FAST 28-3月 -11
SQL> select count(*) from big2_mv;
COUNT(*)
----------
0
五、同一个主表的多物化视图刷新
SQL> create materialized view big2_mv1
2 build immediate
3 refresh force
4 on demand
5 with rowid
6 as select * from big2;
Materialized view created.
SQL> create materialized view big2_mv2
2 build immediate
3 refresh force
4 on demand
5 with rowid
6 as select * from big2;
Materialized view created.
SQL> select count(*) from big2;
COUNT(*)
----------
0
SQL> select count(*) from big2_mv1;
COUNT(*)
----------
0
SQL> select count(*) from big2_mv2;
COUNT(*)
----------
0
SQL> insert into big2 select * from dba_objects;
57798 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from big2;
COUNT(*)
----------
57798
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
57798
SQL> begin
2 dbms_mview.refresh('big2_mv1','fast');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select count(*) from big2_mv1;
COUNT(*)
----------
57798
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
57798
SQL> begin
2 dbms_mview.refresh('big2_mv2','fast');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select count(*) from big2_mv2;
COUNT(*)
----------
57798
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
0
SQL> insert into big2 select * from dba_objects where rownum<101;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
100
SQL> begin
2 dbms_mview.refresh('big2_mv2','fast');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
100
SQL> begin
2 dbms_mview.refresh('big2_mv1','fast');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select count(*) from mlog$_big2;
COUNT(*)
----------
0
Oracle内部记录同一个源表的物化视图日志是否还在另一个刷新中使用,当所有物化视图都刷新过后,才会删除物化视图日志,因而多物化视图交叉刷新,不会受到影响。
附1 刷新组管理
a、添加某物化视图到物化视图组
BEGIN
DBMS_REFRESH.ADD (
name => 'ref_grp',
list => '...');
END;
/
b、移除某物化视图
BEGIN
DBMS_REFRESH.subtract (
name => 'ref_grp',
list => '...');
END;
/
c、删除物化视图组
BEGIN
DBMS_REFRESH.destroy (
name => 'ref_grp');
END;
/
附2 物化视图相关数据字典
user_mviews all_mviews dba_mviews (物化视图的基本信息及最后刷新信息)
user_mview_logs all_mview_logs dba_mview_logs(物化视图日志的信息)
user_refresh all_refresh dba_refresh(刷新组信息)
user_refresh_children all_refresh_children dba_refresh_children (刷新组成员信息)