[转载]Oracle物化视图创建及管理

原文地址：Oracle物化视图创建及管理作者：于士博

一、前言：

在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 （刷新组成员信息）