Oracle 物化视图详解

Oracle 物化视图详解
物化的一般用法物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。
物化视图就是典型的用空间换取查询的效率。如果通过物化视图来提升查询的效率，主要是通过查询重写来进行保证的。
我们来看一个创建物化视图的语句：
CREATE MATERIALIZED VIEW cust_sales_mv  -- Name
  PCTFREE 0  TABLESPACE example          -- Storage options  STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
  BUILD DEFERRED       –- When to build it
  REFRESH COMPLETE ON DEMAND    –- How to refresh the data
  ENABLE QUERY REWRITE –- Use this for query rewrite
      AS SELECT c.cust_id, s.channel_id,   –- Detail query
            SUM(amount_sold) 
     FROM   sales s, customers c       –- Detail tables
     WHERE  s.cust_id = c.cust_id
     GROUP BY c.cust_id, s.channel_id  –- MV keys
     ORDER BY c.cust_id, s.channel_id;
下面我们来逐步的分析下创建无法视图的各个选项。
BUILD DEFERRED/IMMEDIATE
创建方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，只有在刷新以后才生成数据。默认为BUILD IMMEDIATE。
REFRESH COMPLETE/FAST ON COMMIT/DEMAND
刷新模式：
物化视图有二种刷新模式：
在创建时refresh mode是 on demand 还是 on commit。
on demand 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;
on commit  提交触发，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。
创建物化视图时未作指定，则Oracle按 on demand 模式来创建。 
上面说的是刷新的模式，针对于如何刷新，则有三种刷新方法：
完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（FAST）： 采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。
关于快速刷新：Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
物化视图日志：

create table t (id number, name varchar2(30), num number);
create materialized view log on t with rowid, sequence (id, name) including new values ;
SQL> desc MLOG$_T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(30)
 M_ROW$$                                            VARCHAR2(255)
 SEQUENCE$$                                         NUMBER
 SNAPTIME$$                                         DATE
 DMLTYPE$$                                          VARCHAR2(1)
 OLD_NEW$$                                          VARCHAR2(1)
 CHANGE_VECTOR$$                                    RAW(255)
 XID$$                                              NUMBER

M_ROW$$            保存基表的ROWID信息，根据MROW中的信息可以定位到发生DML操作的记录。

SEQUENCE$$       根据DML操作发生的顺序记录序列的编号，当刷新时，根据SEQUENCE中的顺序就可以和基表中的执行顺序保持一致。

SNAPTIME$$        列记录了刷新操作的时间。

DMLTYPE$$        的记录值I、U和D，表示操作是INSERT、UPDATE还是DELETE。

OLD_NEW$$        表示物化视图日志中保存的信息是DML操作之前的值（旧值）还是DML操作之后的值（新值）。除了O和N这两种类型外，对于UPDATE操作，还可能表示为U。
CHANGE_VECTOR$$    记录DML操作发生在那个或那几个字段上
 
当刷新物化视图时，只需要根据SEQUENCE列给出的顺序，通过MROW$$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$$定位到字段，然后根据基表中的数据重复执行DML操作。

create materialized view mv_t_id_name refresh fast as select id, name, count(*) from t group by id, name;

insert into t values (1, 'a', 2);
insert into t values (1, 'b', 3);
insert into t values (2, 'a', 5);
insert into t values (3, 'b', 7);
update t set name = 'c' where id =3;
delete t where id = 2;
set linesize 150
SQL> col M_ROW$$ for a20
SQL> col CHANGE_VECTOR$$ for a20
SQL> col M_ROW$$ for a20
SQL> col CHANGE_VECTOR$$ for a20
SQL> select * from MLOG$_T;

        ID NAME                           M_ROW$$              SEQUENCE$$ SNAPTIME$ D O CHANGE_VECTOR$$           XID$$
---------- ------------------------------ -------------------- ---------- --------- - - -------------------- ----------
         1 a                              AAAVouAAEAAAAeeAAA            1 01-JAN-00 I N FE                   2.8148E+15
         1 b                              AAAVouAAEAAAAeeAAB            2 01-JAN-00 I N FE                   2.8148E+15
         2 a                              AAAVouAAEAAAAeeAAC            3 01-JAN-00 I N FE                   2.8148E+15
         3 b                              AAAVouAAEAAAAeeAAD            4 01-JAN-00 I N FE                   2.8148E+15
         3 b                              AAAVouAAEAAAAeeAAD            5 01-JAN-00 U U 04                   2.8148E+15
         3 c                              AAAVouAAEAAAAeeAAD            6 01-JAN-00 U N 04                   2.8148E+15
         2 a                              AAAVouAAEAAAAeeAAC            7 01-JAN-00 D O 00                   2.8148E+15
SQL> select * from MV_T_ID_NAME;

no rows selected
执行快速刷新：
exec dbms_mview.refresh('MV_T_ID_NAME','F');
select * from MV_T_ID_NAME;
SQL> select * from MV_T_ID_NAME;  
        ID NAME                             COUNT(*)
---------- ------------------------------ ----------
         1 a                                       1
         1 b                                       1
         3 c                                       1
SQL> select * from mlog$_t;

no rows selected

1.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
(2)必须指明ROWID和INCLUDING NEW VALUES；
(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。 
3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX； 
4.必须指定COUNT(*)；
不加COUNT(*)，建立快速刷新的物化视图会成功，但通过执行EXPLAIN_MVIEW过程，可以发现， COUNT(*)对于INSERT操作是可以快速刷新的，不过对于UPDATE和DELETE则会造成快速刷新的失败。
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(*)。  

drop materialized view MV_T_ID_NAME;
drop materialized view log on t;      

SQL> create materialized view log on t with rowid, sequence (id, name,num) including new values ;

Materialized view log created.

SQL>  create materialized view mv_t_id_name refresh fast as select id, name, sum(num) from t group by id, name;

Materialized view created.

insert into t values (1, 'a', 2);
insert into t values (1, 'b', 3);
insert into t values (2, 'a', 5);
insert into t values (3, 'b', 7);
update t set name = 'c' where id =3;
delete t where id = 2;

SQL> exec dbms_mview.refresh('MV_T_ID_NAME','F');
BEGIN dbms_mview.refresh('MV_T_ID_NAME','F'); END;
*
ERROR at line 1:
ORA-32314: REFRESH FAST of "SCOTT"."MV_T_ID_NAME" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1
存在报错
EXECUTE DBMS_ADVISOR.DELETE_TASK('my_tune_mview_task');

VARIABLE my_tune_mview_task VARCHAR2(30);
VARIABLE SQL VARCHAR2(4000);
EXECUTE :my_tune_mview_task := 'mview_task';
EXECUTE :SQL := 'create materialized view mv_t_id_name refresh fast as select id, name, sum(num) from t group by id, name';
 
EXECUTE DBMS_ADVISOR.TUNE_MVIEW (:my_tune_mview_task,:sql);

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:my_tune_mview_task),'KIWI','my_tune_mview_create.sql');

可以查看到的改写后的物化视图
CREATE MATERIALIZED VIEW SCOTT.MV_T_ID_NAME
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SCOTT.T.NAME C1, SCOTT.T.ID C2, SUM("SCOTT"."T"."NUM") M1, COUNT("SCOTT"."T"."NUM")
       M2, COUNT(*) M3 FROM SCOTT.T GROUP BY SCOTT.T.NAME, SCOTT.T.ID;

ENABLE QUERY REWRITE 查询重写（QueryRewrite）：
包括 enable query rewrite 和 disable query rewrite 两种。
分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。
默认为disable query rewrite。

create materialized view mv_t refresh fast enable query rewrite as select id, name, count(*) from t group by id, name;
SQL> select id,name from t group by id,name;

Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     3 |    90 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T |     3 |    90 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        651  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>  drop materialized view mv_t;

Materialized view dropped.

dbms_mview.explain_mview
SQL> @?/rdbms/admin/utlxrw.sql

Table created.

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
REWRITE_TABLE                  TABLE

exec dbms_mview.Explain_Rewrite(QUERY => 'select id,name from t group by id,name',MV => 'MV_T_ID_NAME');

SQL> commit;

Commit complete.

SQL> select message from REWRITE_TABLE where message is not null;

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01026: query rewrite is disabled for, MV_T_ID_NAME