sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

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

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

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

天萃荷净 ++++++ Oracle 物化视图学习笔记之一 ++++++++

首先描述几个术语,如下:

基表:也就是我们常说的master table和master materialized view,这里说明一下,基表并不是说这只有一个表,它是你创建MV时所需要用到的表(可能有多个)或相关的上一级的MV

MV: 也就是物化视图,英文名materizlized view。
源端(master 站点,master MV size): 都是指基表所在的一端(基表所在数据库)
MV端(mv站点,mv site): 物化视图所在地一端(mv所在数据库)

需要注意的是,MV跟普通的view不同,它是有segment存在的,不像view那样是虚拟存在的。你可以再dba_semgents中查到它的对象大小,当然它的好处就是,我们在利用MV进行查询时,不再需要去访问基表了,只需通过访问MV对应的结果集就行了。当然,这里涉及一个刷新机制,MV的数据和基表的数据是定期刷新来完成同步的。后面会讲到涉及的集中mv的刷新机制。

下面是一个简单的创建MV的例子:

SQL> SHOW USER
USER IS "KILLDB"

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

SQL> CREATE materialized VIEW mv_tab1 AS SELECT * FROM mv_master;
Materialized VIEW created.

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50901

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50901

SQL> DELETE FROM mv_master WHERE rownum < 101;
100 ROWS deleted.

SQL> commit;
Commit complete.

SQL> SELECT COUNT(*) FROM mv_master

  COUNT(*)
----------
     50801

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50901

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50901

为什么MV没有同步呢?因为默认的mv刷新模式的DEMAND 如下:
SQL> SELECT OWNER,MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE,FAST_REFRESHABLE
  2  FROM sys.dba_mviews WHERE owner='KILLDB';

OWNER           MVIEW_NAME           REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE
--------------- -------------------- ------ -------- --------- ------------------
KILLDB          MV_TAB1              DEMAND FORCE    IMMEDIATE NO

下面我们进行手工刷新:
SQL> SHOW USER
USER IS "KILLDB"

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

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50801

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50801   ---可以看到,数据同步了。

我们来看看官方文档中对于mv 刷新模式的解释,有3种方式:

DEMAND - Oracle Database refreshes this materialized view whenever an appropriate refresh procedure is called
COMMIT - Oracle Database refreshes this materialized view when a transaction on one of the materialized view's masters commits
NEVER - Oracle Database never refreshes this materialized view

demand: 当调用dbms_mview时才进行刷新。
commit: 当基表有事务commit后就进行刷新同步。
never: Oracleо不刷新MV。

下面我们来创建基于commit的mv看看是怎么样的:

SQL> drop materialized view mv_tab1;
Materialized view dropped.

SQL> create materialized view mv_tab1 refresh force on commit as select * from mv_master;
create materialized view mv_tab1 refresh force on commit as select * from mv_master
                                                                          *
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

发现报错,怎么办呢?关于mv方面的错误,oracle提供了一个脚本utlxmv.SQL,我们借助这个SQL来进行诊断,如下操作:

SQL> conn /AS sysdba
Connected.

SQL> BEGIN
  2  dbms_mview.explain_mview('select * from killdb.mv_master');
  3  END;
  4  /

PL/SQL PROCEDURE successfully completed.

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

MSGTXT
---------------------------------------------------------------------------------------------
the detail TABLE does NOT have a materialized VIEW log

很明显,提示我们需要为改表创建物化视图日志。

SQL> conn killdb/killdb
Connected.

SQL> CREATE materialized VIEW log ON mv_master;
CREATE materialized VIEW log ON mv_master
*
ERROR at line 1:
ORA-12014: TABLE 'MV_MASTER' does NOT contain a PRIMARY KEY CONSTRAINT

我们可以来看看这个错误的具体解释:

[oracle@10gasm ~]$ oerr ora 12014
12014, 00000, "table '%s' does not contain a primary key constraint"
// *Cause:  The CREATE MATERIALIZED VIEW LOG command was issued with the
//          WITH PRIMARY KEY option and the master table did not contain
//          a primary key constraint or the constraint was disabled.
// *Action: Reissue the command using only the WITH ROWID option, create a
//          primary key constraint on the master table, or enable an existing
//          primary key constraint.

这里又涉及一个问题,那就是创建物化视图日志的方式有如下几种:

1) 基于主键
2) 基于rowid
4) 基于object id
4) 基于sequence+rowid+(属性列)

下面来分别描述这4种情况下的创建情况:

1. 基于主键

SQL> ALTER TABLE mv_master ADD CONSTRAINT pk_mv_master PRIMARY KEY (object_id);
ALTER TABLE mv_master ADD CONSTRAINT pk_mv_master PRIMARY KEY (object_id)
                                                               *
ERROR at line 1:
ORA-01449: COLUMN contains NULL VALUES; cannot ALTER TO NOT NULL

SQL> SELECT COUNT(*) FROM mv_master WHERE object_id IS NULL;

  COUNT(*)
----------
         1

SQL> DELETE FROM mv_master WHERE object_id IS NULL;
1 ROW deleted.

SQL> commit;
Commit complete.

SQL> ALTER TABLE mv_master ADD CONSTRAINT pk_mv_master PRIMARY KEY (object_id);
TABLE altered.

SQL> CREATE materialized VIEW mv_tab1 refresh force ON commit AS SELECT * FROM mv_master;
Materialized VIEW created.

SQL> DELETE FROM mv_master WHERE rownum < 101;
100 ROWS deleted.

SQL> commit;
Commit complete.

SQL> SET timing ON
SQL> DELETE FROM mv_master WHERE rownum < 101;
100 ROWS deleted.
Elapsed: 00:00:00.03

SQL> commit;
Commit complete.

Elapsed: 00:00:11.68
SQL> SET autot traceonly
SQL> SET LINES 150
SQL> SELECT COUNT(*) FROM mv_tab1;

Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1259844547
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | ROWS  | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    33   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_MV_MASTER1 | 46897 |    33   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used FOR this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        112  consistent gets
          0  physical reads
          0  redo SIZE
        412  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

SQL> analyze TABLE mv_tab1 compute statistics;
TABLE analyzed.
Elapsed: 00:00:00.66

SQL> SELECT COUNT(*) FROM mv_tab1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1259844547

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | ROWS  | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    30   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_MV_MASTER1 | 50600 |    30   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        112  consistent gets
          0  physical reads
          0  redo SIZE
        412  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

从上面可以看到,mv_tab1在基表进行commit时就会进行同步,同时问题也来了,你可以看到上面commit的时候非常的慢。后面会详细描述mv刷新的几种方式。

这里需要注意一点的是:由于物化视图也是实在存在的对象,所以它也是有统计信息的,从上面的执行计划你可以可发现,不对物化视图进行统计信息的收集,其统计信息是不准确的。

当你创建物化视图日志以后,你会发现速度ON commit速度是比较快的:

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.
Elapsed: 00:00:00.10

SQL> CREATE materialized VIEW log ON mv_master;
Materialized VIEW log created.
Elapsed: 00:00:00.16

SQL> CREATE materialized VIEW mv_tab1 refresh force ON commit AS SELECT * FROM mv_master;
Materialized VIEW created.
Elapsed: 00:00:00.63

SQL> analyze TABLE mv_tab1 compute statistics;
TABLE analyzed.
Elapsed: 00:00:00.53

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50600

Elapsed: 00:00:00.01

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50600

Elapsed: 00:00:00.01

SQL> DELETE FROM mv_master WHERE rownum < 101;

100 ROWS deleted.

Elapsed: 00:00:00.06

SQL> commit;
Commit complete.

Elapsed: 00:00:00.10

SQL> SELECT COUNT (*) FROM mv_master;

  COUNT(*)
----------
     50500

Elapsed: 00:00:00.01

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50500

Elapsed: 00:00:00.01

2) 基于rowid的方式

SQL> DROP materialized VIEW log ON mv_master;
Materialized VIEW log dropped.

Elapsed: 00:00:00.05

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.
Elapsed: 00:00:00.06

SQL> CREATE materialized VIEW log ON mv_master WITH rowid;
Materialized VIEW log created.
Elapsed: 00:00:00.07

SQL> CREATE materialized VIEW mv_tab1 refresh force ON commit AS SELECT * FROM mv_master;
Materialized VIEW created.
Elapsed: 00:00:02.26

SQL> analyze TABLE mv_tab1 compute statistics;
TABLE analyzed.
Elapsed: 00:00:00.45

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50500

Elapsed: 00:00:00.02

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50500

Elapsed: 00:00:00.01

SQL> DELETE FROM mv_master WHERE rownum < 101;
100 ROWS deleted.
Elapsed: 00:00:00.04

SQL> commit;
Commit complete.
Elapsed: 00:00:11.84

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50400

Elapsed: 00:00:00.01

SQL>  SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50400

Elapsed: 00:00:00.02

可以看到基于rowid的方式,基表进行commit时是非常慢的,还有个需要注意的是,当你基表进行move或shrink等操作后,rowid会发现变化的。

SQL> ALTER TABLE mv_master move;
TABLE altered.
Elapsed: 00:00:02.17

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50300

Elapsed: 00:00:00.01

SQL>  SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50300

Elapsed: 00:00:00.02

SQL> DELETE FROM mv_master WHERE rownum < 101;
DELETE FROM mv_master WHERE rownum < 101
*
ERROR at line 1:
ORA-01502: INDEX 'KILLDB.PK_MV_MASTER' OR partition OF such INDEX IS IN unusable state

Elapsed: 00:00:00.02

SQL> ALTER INDEX PK_MV_MASTER rebuild;
INDEX altered.
Elapsed: 00:00:11.54

SQL> DELETE FROM mv_master WHERE rownum < 101;
100 ROWS deleted.
Elapsed: 00:00:00.04

SQL> commit;
Commit complete.
Elapsed: 00:00:11.98

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50200

Elapsed: 00:00:00.01

SQL>  SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50200

Elapsed: 00:00:00.01

3) 基于object id

SQL> DROP  materialized VIEW log ON mv_master;

Materialized VIEW log dropped.
Elapsed: 00:00:00.02

SQL> DROP  materialized VIEW mv_tab1;

Materialized VIEW dropped.
Elapsed: 00:00:00.34

SQL> DROP TABLE mv_master;
TABLE dropped.
Elapsed: 00:00:00.28

SQL> SET timing off
SQL>  CREATE TYPE  mv1 AS object(id NUMBER,owner varchar2(30))
  2  /

TYPE created.

SQL> CREATE TABLE mv_master OF mv1;
TABLE created.

SQL> CREATE materialized VIEW log ON mv_master WITH object id;
Materialized VIEW log created.

SQL> CREATE materialized VIEW mv_tab1 AS SELECT * FROM mv_master;
Materialized VIEW created.

SQL> INSERT INTO mv_master SELECT object_id,owner FROM sys.dba_objects;
50908 ROWS created.

SQL> commit;
Commit complete.

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50908

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(*)
----------
     50908

4) 基于rowid+sequence+column

SQL> DROP materialized VIEW mv_tab1;
Materialized VIEW dropped.

SQL> DROP materialized VIEW log ON mv_master;
Materialized VIEW log dropped.

SQL> CREATE materialized VIEW log ON mv_master WITH rowid,SEQUENCE (id, owner) including NEW VALUES
Materialized VIEW log created.

SQL> CREATE materialized VIEW mv_tab1 AS SELECT * FROM mv_master;
Materialized VIEW created.

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     50908

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50908

SQL>  DELETE FROM mv_master WHERE rownum < 10001;
10000 ROWS deleted.

SQL> commit;
Commit complete.

SQL> SELECT COUNT(*) FROM mv_master;

  COUNT(*)
----------
     40908

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     50908

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

SQL> SELECT COUNT(*) FROM mv_tab1;

  COUNT(*)
----------
     40908

简单的总结一下,基于rowid的方式其实都是向后兼容的,从8i以后基本上就不用基于rowid的方式了,当然也不排除一些特殊的场景仍然可以使用。

补充:

关于表mv_capabilities_table的说明,在10g中是没有,只能去参考utlxmv.sql脚本,在11g中官方文档就已经进行明确说明了
可以参考如下链接http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG8223

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

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

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

Oracle研究中心

关键词:

materizlized view

Oracle materizlized view使用教程