Oracle materizlized view Study (1)

++++++ 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> 
SQL> create materialized view mv_tab1 as select * from mv_master;

Materialized view created.

SQL> 
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
  2  ;

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

SQL> select count(*) from mv_tab1;

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

SQL> select count(*) from mv_tab1;

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

SQL> 
为什么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： 不刷新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> 

很明显，提示我们需要为改表创建物化视图日志。
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


SQL>  
复制

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

[oracle@10gasm ~]$ 
[oracle@10gasm ~]$ oerr ora 12014
12014, 00000, "table '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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.
[oracle@10gasm ~]$


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

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> 
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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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

SQL> 

从上面可以看到，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
SQL> 
复制

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> 
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
SQL> 

可以看到基于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> 
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

SQL> 
复制

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 
  2  /

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> 
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