Oracle物化视图的一般使用

(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到确认；

(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；

(3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；

创建语句：create materialized view mv_name as select * from table_name

因为物化视图由于是物理真实存在的，故可以创建索引。

分为两种：build immediate 和 build deferred，

build immediate是在创建物化视图的时候就生成数据。

build deferred则在创建时不生成数据，以后根据需要在生成数据。

如果不指定，则默认为build immediate。

包括 enable query rewrite 和 disable query rewrite 两种。

分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

默认为disable query rewrite。

 

查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。

/*+ REWRITE(MVIEW_NAME) */  使用物化视图进行查询重写。

[sql] view plain copy

1. SQL> select e.empno, e.ename, e.job  
2.   2    from emp e, dept d  
3.   3   where e.deptno = d.deptno  
4.   4     and d.dname = 'RESEARCH';  
5.   
6.      EMPNO ENAME      JOB  
7. ---------- ---------- ---------  
8.       7566 JONES      MANAGER  
9.       7902 FORD       ANALYST  
10.       7876 ADAMS      CLERK  
11.       7369 SMITH      CLERK  
12.       7788 SCOTT      ANALYST  
13.   
14.   
15. 执行计划  
16. ----------------------------------------------------------  
17. Plan hash value: 844388907  
18.   
19. ----------------------------------------------------------------------------------------  
20. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
21. ----------------------------------------------------------------------------------------  
22. |   0 | SELECT STATEMENT             |         |     5 |   170 |     6  (17)| 00:00:01 |  
23. |   1 |  MERGE JOIN                  |         |     5 |   170 |     6  (17)| 00:00:01 |  
24. |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2   (0)| 00:00:01 |  
25. |   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |  
26. |*  4 |   SORT JOIN                  |         |    14 |   294 |     4  (25)| 00:00:01 |  
27. |   5 |    TABLE ACCESS FULL         | EMP     |    14 |   294 |     3   (0)| 00:00:01 |  
28. ----------------------------------------------------------------------------------------  
29.   
30. Predicate Information (identified by operation id):  
31. ---------------------------------------------------  
32.   
33.    2 - filter("D"."DNAME"='RESEARCH')  
34.    4 - access("E"."DEPTNO"="D"."DEPTNO")  
35.        filter("E"."DEPTNO"="D"."DEPTNO")  
36.   
37. SQL> set autotrace off;  
38. SQL> create materialized view sales_time_product_mv enable query rewrite as  
39.   select e.empno, e.ename, e.job  
40.     from emp e, dept d  
41.    where e.deptno = d.deptno  
42.   5       and d.dname = 'RESEARCH';  
43.   
44. 实体化视图已创建。  
45.   
46. SQL> set autotrace on explain;  
47. SQL> select e.empno, e.ename, e.job  
48.   2    from emp e, dept d  
49.   3   where e.deptno = d.deptno  
50.   4     and d.dname = 'RESEARCH';  
51.   
52.      EMPNO ENAME      JOB  
53. ---------- ---------- ---------  
54.       7566 JONES      MANAGER  
55.       7902 FORD       ANALYST  
56.       7876 ADAMS      CLERK  
57.       7369 SMITH      CLERK  
58.       7788 SCOTT      ANALYST  
59.   
60.   
61. 执行计划  
62. ----------------------------------------------------------  
63. Plan hash value: 663088863  
64.   
65. ------------------------------------------------------------------------------------------------------  
66. | Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  
67. ------------------------------------------------------------------------------------------------------  
68. |   0 | SELECT STATEMENT             |                       |     5 |   130 |     2   (0)| 00:00:01 |  
69. |   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_TIME_PRODUCT_MV |     5 |   130 |     2   (0)| 00:00:01 |  
70. ------------------------------------------------------------------------------------------------------  
71.   
72. Note  
73. -----  
74.    - dynamic sampling used for this statement (level=2)  

我在物化视图中使用了一个rewrite提示来打开查询重写转换。

create materialized view view_name
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)

]

AS subquery;

 

 

创建物化视图需要的权限：

grant create materialized view to user_name; 

 

在源表建立物化视图日志：

create materialized view log on test_table  

tablespace test_space -- 日志空间  

with primary key;     -- 指定为主键类型

 

在目标数据库上创建MATERIALIZED VIEW：

create materialized view mv_materialized_test refresh force on demand start with sysdate next

to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as

select * from user_info; --这个物化视图在每天10：25进行刷新 

 

修改刷新时间：

alter materialized view mv_materialized_test refresh force on demand start with sysdate 

next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 23:00:00'),'dd-mm-yyyy hh24:mi:ss');

或

alter materialized view mv_materialized_test refresh force on demand start with sysdate 

next trunc(sysdate,'dd')+1+1/24; -- 每天1点刷新 

建立索引：

create index IDX_MMT_IU_TEST

on mv_materialized_test(ID,UNAME)  

tablespace test_space; 

删除物化视图及日志：

drop materialized view log on test_table;    --删除物化视图日志： 

drop materialized view mv_materialized_test; --删除物化视图
***************************************************************************

1、on demand:使用DBMS_MVIEW包中的存储过程启用手工刷新（默认设置）

refresh [fast|complete|force] 视图刷新的方式:

complete:全部刷新。相当于重新执行一次创建视图的查询语句。

 

fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on （主表名）。（多张表时，此语句也生效，创建后，原来的表中会多出两类视图表：MLOG$_table_name和RUPD$_table_name）

 

force: 这是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。

 

2、on commit：在事务提交后刷新

使用情况

⑴仅用于快速刷新的物化视图

⑵需要on commit refresh对象权限

⑶如果刷新失败需要进行手工刷新

3、never：禁止物化视图刷新

 

在计划时间进行刷新：使用start with 和next选项。从指定的时间开始，每隔一段时间（由next指定）就刷新一次；

比如说我们要全刷新一张mv_test物化视图：

begin
     dbms_mview.refresh(TAB=>'MV_TEST',
                                       METHOD=>'COMPLETE',
                                       PARALLELISM=>8);
end;
/

增量刷新就不需要使用什么并行了，通常情况下，是没有那个必要的。

begin
     dbms_mview.refresh(TAB=>'MV_TEST',
                                       METHOD=>'FAST',
                                       PARALLELISM=>1);
end;
/

 

或者，也可以这样执行：

exec dbms_mview.refresh('MV_TEST','F');

 

 

create matherialized view emp_data

pctfree 5 

tablespace example

storage (initial 50K next 50K)

refresh fast next sysdate + 7

as select ...;

 

create matherialized view emp_data

pctfree 5 

tablespace example

using index storage (initial 25K next 25K)

refresh start with round(sysdate + 1) + 11/24

next next_day(trunc(sysdate),'MONDAY') + 15/24

as select * from [email protected] union

select * from [email protected];

***************************************************************************

公司的项目中遇到需要从其它系统的数据库中取数据进行统计分析的问题，初步选择使用ORACLE的ODI工具进行抽数，但是对方提供的数据库用户下没有任何对象，只是有查询所有表的权限，因此无法做数据反向。于是决定使用物化视图，把对方数据库中的数据拿过来，虽然数据量比较大，但是每月只拿一次，而且如果设置成增量更新，也不会太慢。现在记录下物化视图的创建过程（以一张表为例）。

 

一、准备条件以及备注

      假设双方数据库都是ORACLE10g，需要同步过来的表名叫：GG_ZLX_ZHU，对方数据库用户名：username，密码：password，SID：CPEES。

 

二、开始干活

      1、首先要创建DB_LINK

      CREATE DATABASE LINK to_cpees

      CONNECT TO "username" identified by "password"

      using "CPEES"

      其中CPEES为新建的到对方数据库的TNS。执行，现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。

 

       2、创建物化视图快速刷新日志

       因为上面说过，以后视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在住表上建立物化视图日志。

       CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU
       WITH PRIMARY KEY
       INCLUDING NEW VALUES;

       (上面的SQL要在远程数据库上执行，不能在本地执行)

 

       3、创建物化视图

       物化视图，从名字上面来开，它应该是属于视图，但是确实物化。其物化是针对普通视图并没有真正的物理存储而言，其实可以简单的把物化视图看做一个物理表（不再做具体解释）。

       CREATE MATERIALIZED VIEW GG_ZLX_ZHU --创建物化视图
       BUILD IMMEDIATE  --在视图编写好后创建
       REFRESH FAST WITH PRIMARY KEY  --根据主表主键增量刷新（FAST，增量）
       ON DEMAND  -- 在用户需要时，由用户刷新
       ENABLE QUERY REWRITE  --可读写
       AS
       SELECT * FROM [email protected]_CPEES; --查询语句

    

       4、视图刷新

       根据业务需要，每月不定时刷新，所以不能是JOB，而且数量多，所以也不能一个一个刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过来，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。如下：

 

        CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS

        BEGIN

             DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');

        END P_MVIEW_REFRESH;

 

        或者使用

        CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS

 

        BEGIN

             DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');

        END P_MVIEW_REFRESH;

        注意:

        1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对

        每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。

        2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量

        刷新。

        3、因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。

        4、以上文章中红色是为可替换的，大家可以根据自己项目需求来修改。

 

 

希望上面的内容对大家能有帮助。

        忘了写删除方法了，日志和物化视图要分开删除

        DROP MATERIALIZED VIEW LOG ON [email protected];

        DROP MATERIALIZED VIEW GG_ZLX_ZHU;

**********************************************************************************************
Oracle如何根据物化视图日志快速刷新物化视图
http://blog.itpub.net/4227/viewspace-68566/

查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。

/*+ REWRITE(MVIEW_NAME) */  使用物化视图进行查询重写。

我在物化视图中使用了一个rewrite提示来打开查询重写转换。

公司的项目中遇到需要从其它系统的数据库中取数据进行统计分析的问题，初步选择使用ORACLE的ODI工具进行抽数，但是对方提供的数据库用户下没有任何对象，只是有查询所有表的权限，因此无法做数据反向。于是决定使用物化视图，把对方数据库中的数据拿过来，虽然数据量比较大，但是每月只拿一次，而且如果设置成增量更新，也不会太慢。现在记录下物化视图的创建过程（以一张表为例）。

一、准备条件以及备注

      假设双方数据库都是ORACLE10g，需要同步过来的表名叫：GG_ZLX_ZHU，对方数据库用户名：username，密码：password，SID：CPEES。

二、开始干活

      1、首先要创建DB_LINK

      CREATE DATABASE LINK to_cpees

      CONNECT TO "username" identified by "password"

      using "CPEES"

      其中CPEES为新建的到对方数据库的TNS。执行，现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。

       2、创建物化视图快速刷新日志

       因为上面说过，以后视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在住表上建立物化视图日志。

       CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU
       WITH PRIMARY KEY
       INCLUDING NEW VALUES;

       (上面的SQL要在远程数据库上执行，不能在本地执行)

       3、创建物化视图

       物化视图，从名字上面来开，它应该是属于视图，但是确实物化。其物化是针对普通视图并没有真正的物理存储而言，其实可以简单的把物化视图看做一个物理表（不再做具体解释）。

       CREATE MATERIALIZED VIEW GG_ZLX_ZHU --创建物化视图
       BUILD IMMEDIATE  --在视图编写好后创建
       REFRESH FAST WITH PRIMARY KEY  --根据主表主键增量刷新（FAST，增量）
       ON DEMAND  -- 在用户需要时，由用户刷新
       ENABLE QUERY REWRITE  --可读写
       AS
       SELECT * FROM [email protected]_CPEES; --查询语句

       4、视图刷新

       根据业务需要，每月不定时刷新，所以不能是JOB，而且数量多，所以也不能一个一个刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过来，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。如下：

        CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS

        BEGIN

             DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');

        END P_MVIEW_REFRESH;

        或者使用

        CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS

        BEGIN

             DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');

        END P_MVIEW_REFRESH;

        注意:

        1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对

        每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。

        2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量

        刷新。

        3、因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。

        4、以上文章中红色是为可替换的，大家可以根据自己项目需求来修改。

希望上面的内容对大家能有帮助。

        忘了写删除方法了，日志和物化视图要分开删除

        DROP MATERIALIZED VIEW LOG ON [email protected];

        DROP MATERIALIZED VIEW GG_ZLX_ZHU;