sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库物化视图materizlized view使用教程

时间:2016-12-10 18:20   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库materizlized view的研究笔记,详细介绍重点讲解物化视图的刷新方式。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: Oracle materizlized view Study (2)

重点讲解物化视图的刷新方式,这也是一个比较难的知识点,和大家以前学习。因为很长一段时间不用物化视图,说真的,我基本上都忘的差不多了,最近的项目涉及到物化视图的一个几个库(超过3T)的迁移,所以温习下。

我们通过查看试图dba_mviews的REFRESH_METHOD字段可以发现该自动有如下几种属性,换句话说也就是说物化视图有如下几种刷新方式:

COMPLETE (C) - Materialized view is completely refreshed from the masters
FORCE ( ) - Oracle Database performs a fast refresh if possible, otherwise a complete refresh
FAST (F) - Oracle Database performs an incremental refresh applying changes that correspond to changes in the masters since the last refresh
NEVER (N) - User specified that the Oracle Database should not refresh this materialized view

简单在总结为如下几点:

compelete: 完全刷新,在压力比较大的情况下,完全刷新可能会带来很多问题,特别是跨站点的情况,对网络要求很高。
force:强制刷新,竟可能的使用增量快速刷新,如果不能则使用完全刷新。默认值是force。
fast: 增量快速刷新。
never:从不刷新

还有一个字段BUILD_MODE,这是表明创建mv的方式,说白了就是指在创建mv时是否生成数据。默认有如下几种:

IMMEDIATE - Populated from the masters during creation
DEFERRED - Not populated during creation. Must be explicitly populated later by the user.
PREBUILT - Populated with an existing table during creation. The relationship of the contents of this prebuilt table to the materialized view's masters is unknown to the Oracle Database.

简单总结为如下几点:

immediate:创建mv时就生成数据了,也就是在创建mv时就会进行一次完全刷新,同步数据。默认方式。
deferred: 在创建mv时不生成数据,后面跟你的操作实际需要才生成数据,换句话说,使用这种方式创建mv时,当你查询mv时数据是空的,比如你手工刷新以后,才能查到数据。
prebuilt: 创建时需要先存在跟物化视图存在相同的对象,不然会报错ora-12059。

如下例子:

SQL> CREATE TABLE t1 AS SELECT * FROM sys.dba_objects WHERE rownum < 10001;
TABLE created.

SQL> CREATE TABLE t2 AS SELECT * t1;
TABLE created.

SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY(object_id) ;
TABLE altered.

SQL> ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY(object_id) ;
TABLE altered.

SQL> CREATE materialized VIEW log ON t1;
Materialized VIEW log created.

SQL> CREATE materialized VIEW log ON t2;
Materialized VIEW log created.

SQL> CREATE materialized VIEW mv_tab1 refresh fast ON commit  AS
  2  SELECT a.owner,b.object_name FROM t1 a,t2 b
  3  WHERE a.object_id=b.object_id;
SELECT a.owner,b.object_name FROM t1 a,t2 b
                                  *
ERROR at line 2:
ORA-12052: cannot fast refresh materialized VIEW KILLDB.MV_TAB1

---conn /as sysdba

SQL> DELETE mv_capabilities_table;
92 ROWS deleted.

SQL> commit;
Commit complete.

SQL> BEGIN
  2   dbms_mview.explain_mview('select a.owner,b.object_name from killdb.t1 a,killdb.t2 b where a.object_id=b.object_id');
  3  END;
  4  /

PL/SQL PROCEDURE successfully completed.

SQL> SELECT msgtxt FROM mv_capabilities_table WHERE capability_name = 'REFRESH_FAST_AFTER_INSERT';

MSGTXT
------------------------------------------------------------------------------------------------------------------------
the SELECT list does NOT have the rowids OF ALL the detail TABLES
mv log must have ROWID
mv log must have ROWID

—conn killdb/killdb

SQL> CREATE materialized VIEW mv_tab1 refresh force ON commit AS
  2  SELECT a.owner,b.object_name FROM t1 a,t2 b
  3  WHERE a.object_id=b.object_id;

Materialized VIEW created.

SQL> SELECT owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE
  2  FROM sys.dba_mviews WHERE MVIEW_NAME='MV_TAB1';

OWNER           MVIEW_NAME           R REFRES REFRESH_ BUILD_MOD
--------------- -------------------- - ------ -------- ---------
KILLDB          MV_TAB1              N COMMIT FORCE    IMMEDIATE

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
      9562

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.

SQL> CREATE materialized VIEW mv_tab1 build  deferred refresh force ON commit  AS
  2  SELECT a.owner,b.object_name FROM t1 a,t2 b
  3  WHERE a.object_id=b.object_id;

Materialized VIEW created.

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
         0

SQL>  EXEC dbms_mview.refresh('MV_TAB1');
PL/SQL PROCEDURE successfully completed.

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
      9562

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.

SQL> CREATE materialized VIEW mv_tab1 ON prebuilt TABLE WITH reduced PRECISION AS
  2  SELECT a.owner,b.object_name FROM t1 a,t2 b
  3  WHERE a.object_id=b.object_id;
CREATE materialized VIEW mv_tab1 ON prebuilt TABLE WITH reduced PRECISION AS
         *
ERROR at line 1:
ORA-12059: prebuilt TABLE "KILLDB"."MV_TAB1" does NOT exist

[ora10g@killdb ~]$ oerr ora 12059
12059, 00000, "prebuilt table \"%s\".\"%s\" does not exist"
// *Cause:  The specified prebuilt TABLE did NOT exist.
// *Action: Reissue the SQL command USING BUILD IMMEDIATE, BUILD DEFERRED, OR
//          ensure that the prebuilt TABLE EXISTS.

SQL>  CREATE materialized VIEW mv_tab1 refresh force ON commit AS
  2   SELECT a.owner,b.object_name FROM t1 a,t2 b
  3   WHERE a.object_id=b.object_id;

Materialized VIEW created.

SQL> CREATE TABLE mv_tab2 AS  SELECT a.owner,b.object_name FROM t1 a,t2 b
  2   WHERE a.object_id=b.object_id;

TABLE created.

SQL> CREATE materialized VIEW mv_tab2 ON prebuilt TABLE WITH reduced PRECISION AS
  2   SELECT a.owner,b.object_name FROM t1 a,t2 b
  3   WHERE a.object_id=b.object_id;

Materialized VIEW created.

SQL> SELECT owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE
  2  FROM sys.dba_mviews WHERE owner='KILLDB';

OWNER           MVIEW_NAME           R REFRES REFRESH_ BUILD_MOD
--------------- -------------------- - ------ -------- ---------
KILLDB          MV_TAB1              N COMMIT FORCE    IMMEDIATE
KILLDB          MV_TAB2              N DEMAND FORCE    PREBUILT

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
      9562

SQL> SELECT COUNT(*) FROM mv_tab2;

  COUNT(*)
----------
      9562

SQL> DELETE FROM t1 WHERE object_id < 101;
99 ROWS deleted.

SQL>  DELETE FROM t2 WHERE object_id < 101;
99 ROWS deleted.

SQL> commit;
Commit complete.

SQL>  SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
      9463

SQL> SELECT COUNT(*) FROM mv_tab2;

  COUNT(*)
----------
      9562

SQL> EXEC dbms_mview.refresh('MV_TAB2');   
PL/SQL PROCEDURE successfully completed.

SQL> SELECT COUNT(*) FROM mv_tab2;

  COUNT(*)
----------
      9463

由于通常是用快速刷新,所以这里重点描述下快速刷新。要创建快速刷新的物化视图,需要满足一些列条件:

所以快速刷新的物化视图必须满足下面2点:
1.物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。


只包含连接的物化视图:

1.必须满足所有快速刷新物化视图都满足的条件;
2.不能包括GROUP BY语句或聚集操作;
3.如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上;
4.如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作。
5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。


从我们前面的例子都可以看到,必须创建物化视图日志,且必须是基于rowid方式,不然就会报错。
如果是包含子查询的物化视图,就更复杂了。看下面的例子:

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.

SQL> DROP materialized VIEW mv_tab2;
Materialized VIEW dropped.

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.

SQL> DROP materialized VIEW mv_tab2;
Materialized VIEW dropped.

SQL> CREATE TABLE t1 AS SELECT * FROM sys.dba_objects;
TABLE created.

SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY(object_id) ;
TABLE altered.

SQL> CREATE materialized VIEW log ON t1 WITH PRIMARY KEY;
Materialized VIEW log created.

SQL>  CREATE materialized VIEW mv_tab1 REFRESH FAST FOR UPDATE AS
  2  SELECT * FROM t1 WHERE object_id > 10000 AND object_id < 30001;

Materialized VIEW created.

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     19890

SQL> DELETE FROM t1 WHERE object_id > 20000 AND object_id < 25000;
4999 ROWS deleted.

SQL> commit;
Commit complete.

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     19890

SQL> EXEC dbms_mview.refresh('MV_TAB1');
PL/SQL PROCEDURE successfully completed.

SQL>  SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     14891

关于物化视图快速刷新涉及子查询的情况,有一些限制,如下(来自官方文档):

1)Materialized views must be primary key materialized views.
   ---基表必须存在主键

2)The master's materialized view log must include certain columns referenced in the subquery. For information about
   which columns must be included, see "Logging Columns in the Materialized View Log".
   ---基表的物化视图日志必须包含子查询涉及的列。

3) If the subquery is many to many or one to many, join columns that are not part of a primary key must be included
   in the materialized view log of the master. This restriction does not apply to many to one subqueries.
   ---对于多对多或一对多的子查询,join 列可以不是主键的一部分,但是必须是基表物化视图日志的一部分。多对一的情况不存在这个限制。

4) The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOT EXISTS condition.
   ---子查询必须是积极的,例如你的条件可以使用exists,但是不能使用not exists。

5) The subquery must use EXISTS to connect each nested level (IN is not allowed).

   ---对于嵌套的子查询,必须使用exists,不能使用in。
6) Each table can be in only one EXISTS expression.

   ---每个表只能被exists引用1次。
7) The join expression must use exact match or equality comparisons (that is, equi-joins).

   --连接表达式必须使用精确匹配或"=" 进行操作。
8) Each table can be joined only once within the subquery.

   ---每个表在子查询中只能被join 1次。
9  A primary key must exist for each table at each nested level.
   ---对于嵌套的字句每一层涉及的表都必须存在主键。

10) Each nested level can only reference the table in the level above it.
   ---每个嵌套层只能引用其上层嵌套的表。
11) Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row.
    Multiple OR conditions within a subquery can be connected with an AND condition.
   ---子查询中可以包含and条件,如果存在or,那么每个条件只能引用一个列。对于多个or存在的情况,可以和and 条件      进行关联。

12) All tables referenced in a subquery must reside in the same master site or master materialized view site.
   ---子查询中所有引用的表在master站点或master MV站点中都必须是存在的。

如果不满足条件,可能包如下类似的很多错误:

SQL> CREATE materialized VIEW mv_tab3 refresh fast ON commit AS
  2  SELECT * FROM t3  WHERE  EXISTS (SELECT * FROM t4 WHERE object_id LIKE '2%')
  3  AND object_id > 10000 AND object_id < 25000;
AND object_id > 10000 AND object_id < 25000
                                      *
ERROR at line 3:
ORA-12014: TABLE 'T4' doehttp://www.oracleplus.nets NOT contain a PRIMARY KEY CONSTRAINT

SQL> CREATE materialized VIEW mv_tab3 refresh fast ON commit AS
  2  SELECT * FROM t3  WHERE  EXISTS (SELECT * FROM t4 WHERE object_id LIKE '2%')
  3  AND object_id > 10000 AND object_id < 25000;
AND object_id > 10000 AND object_id < 25000
                                      *
ERROR at line 3:
ORA-12014: TABLE 'T4' does NOT contain a PRIMARY KEY CONSTRAINT

SQL> ALTER TABLE t4  ADD CONSTRAINT pk_t4 PRIMARY KEY(object_id) ;
TABLE altered.

SQL> CREATE materialized VIEW mv_tab3 refresh fast ON commit AS
  2  SELECT * FROM t3  WHERE  EXISTS (SELECT * FROM t4 WHERE object_id LIKE '2%')
  3  AND object_id > 10000 AND object_id < 25000;
AND object_id > 10000 AND object_id < 25000
                                      *
ERROR at line 3:
ORA-23413: TABLE "KILLDB"."T4" does NOT have a materialized VIEW log

SQL> CREATE materialized VIEW log ON t4 WITH  PRIMARY KEY;
Materialized VIEW log created.

SQL> CREATE materialized VIEW mv_tab3 refresh fast ON commit AS
  2  SELECT * FROM t3  WHERE  EXISTS (SELECT * FROM t4 WHERE object_id LIKE '2%')
  3  AND object_id > 10000 AND object_id < 25000;
AND object_id > 10000 AND object_id < 25000
                                      *
ERROR at line 3:
ORA-12054: cannot SET the ON COMMIT refresh attribute FOR the materialized VIEW

pre
最后来讲讲关于刷新方面的一些操作,通常我们可以利用dbms_mview.refresh 包,如下:

EXEC dbms_mview.refresh('mv_tab1'); --不指定参数,是根据你创建物化视图的REFRESH字句来进行的。
EXEC dbms_mview.refresh('mv_tab1','F'); --快速刷新
EXEC dbms_mview.refresh('mv_tab1','C'); --完全刷新

那么我们如何进行定时刷新? 有几种方式,利用dbms_job,这里主要将另外一种方式,如下:
<pre lang="sql">

SQL>  CREATE materialized VIEW mv_tab4 refresh START WITH sysdate NEXT sysdate+1/24
  2   AS SELECT * FROM t4 WHERE object_id  < 30001;

Materialized VIEW created.

SQL> SELECT job,next_date,next_sec,INTERVAL,what  FROM user_jobs;

   JOB NEXT_DATE NEXT_SEC         INTERVAL         WHAT
------ --------- ---------------- ---------------- ---------------------------------------------
    21 22-AUG-12 04:13:38         sysdate+1/24     dbms_refresh.refresh('"KILLDB"."MV_TAB4"');

另外,关于快速刷新,还涉及到很多内容,如下内容来自老杨的博客:

+++++++++ 关于包含聚合的物化视图

1.必须满足所有快速刷新物化视图都满足的条件;
2.物化视图查询的所有表必须建立物化视图日志,且物化视图日志必须满足下列限制:

(1)包含物化视图查询语句中的所有列,包括SELECT列表中的列和WHERE语句中的列;
(2)必须指明ROWID和INCLUDING NEW VALUES;
(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作(即不是只包含INSERT操作),那么物化视图日志应该包括SEQUENCE。

3.允许的聚集函数包括:SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX;
4.必须指定COUNT(*);
5.如果指明了除COUNT之外的聚集函数,则COUNT(expr)也必须存在;

比如:包含SUM(a),则必须同时包含COUNT(a)。

6.如果指明了VARIANCE(expr)或STDDEV(expr),除了COUNT(expr)外,SUM(expr)也必须指明;

Oracle推荐同时包括SUM(expr*expr)。

7.SELECT列表中必须包括所有的GROUP BY列;
8.当物化视图属于下面的某种情况,则快速刷新只支持常规DML插入和直接装载,这种类型的物化视图又称为INSERT-ONLY物化视图;

物化视图包含MIN或MAX聚集函数;
物化视图包含SUM(expr),但是没有包括COUNT(expr);
物化视图没有包含COUNT(*)。

注意:如果建立了这种物化视图且刷新机制是ON COMMIT的,则会存在潜在的问题。当出现了UPDATE或DELETE语句,除非手工完全刷新解决这个问题,
     否则物化视图至此以后都不再自动刷新,且不会报任何错误。

9.如果包含inline views、outer joins、self joins或grouping set,则兼容性的设置必须在9.0以上;
10.如果物化视图建立在视图或子查询上,则要求视图必须可以完全合并的。
11.如果没有外连接,则对WHERE语句没有限制。如果包含外连接,则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图,
   快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件:

SELECT语句列表中应该包含GROUPING标识符:可以是GROUP BY表达式中所有列的GROUPING_ID函数,也可以是GROUP BY表达式中每一列的GROUPING函数;

例如:GROUP BY语句为:GROUP BY CUBE(a, b),则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。

GROUP BY不能产生重复的GROUPING。

比如:GROUP BY a, ROLLUP(a, b)则不支持快速刷新,因为包含了重复的GROUPING:(a), (a, b), (a)。

++++++++ 包含UNION ALL的物化视图:

1.UNION ALL操作必须在查询的顶层。可以有一种情况例外:UNION ALL在第二层,而第一层的查询语句为SELECT * FROM;
2.被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件;
3.SELECT列表中必须包含一列维护列,叫做UNION ALL标识符,每个UNION ALL分支的标识符列应包含不同的常量值;
4.不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询;
5.不支持基于分区改变跟踪(PCT)的刷新;
6.兼容性设置应设置为9.2.0。

++++++++ 嵌套物化视图:

1. 嵌套物化视图的每层都必须满足快速刷新的限制条件;
2. 对于同时包含聚集和连接的嵌套物化视图,不支持ON COMMIT的快速刷新。
关于物化视图的内容非常多,详细的东西参考官方文档,老杨的这部分内容其实都有些老了,部分是9i的。
另外还有MV GROUP等等,不多说了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库物化视图materizlized view使用教程

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1381.html

Oracle研究中心

关键词:

深入研究materizlized view

Oracle数据库物化视图学习笔记