Oracle 学习之性能优化（十一）物化视图
物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。在数据仓库中，还经常使用查询重写（query rewrite）机制，这样不需要修改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。
物化视图和表一样可以直接进行查询。物化视图本身也可以分区。
物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。
物化视图可以分为以下三种类型：包含聚集的物化视图；只包含连接的物化视图；嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。
创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明：
创建方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。
查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。
刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。
在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。
对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。
创建物化视图
SQL> conn / as sysdbaConnected.SQL> grant create materialized view to scott;Grant succeeded.SQL> conn scott/tigerConnected.SQL> create materialized view mv_emp as select * from emp;Materialized view created.
物化视图默认的完整语句如下
CREATE MATERIALIZED VIEW SCOTT.MV_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )NOCACHELOGGINGNOCOMPRESSNOPARALLELBUILD IMMEDIATEUSING INDEX TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )REFRESH FORCE ON DEMANDWITH PRIMARY KEYAS /* Formatted on 2015/9/8 15:28:44 (QP5 v5.252.13127.32847) */SELECT "EMP"."EMPNO" "EMPNO", "EMP"."ENAME" "ENAME", "EMP"."JOB" "JOB", "EMP"."MGR" "MGR", "EMP"."HIREDATE" "HIREDATE", "EMP"."SAL" "SAL", "EMP"."COMM" "COMM", "EMP"."DEPTNO" "DEPTNO" FROM "EMP" "EMP";COMMENT ON MATERIALIZED VIEW SCOTT.MV_EMP IS 'snapshot table for snapshot SCOTT.MV_EMP';CREATE UNIQUE INDEX SCOTT.PK_EMP1 ON SCOTT.MV_EMP(EMPNO)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )NOPARALLEL;
往基本中插入数据
SQL> INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO - 1000, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM scott.emp; 14 rows created.SQL> commit;Commit complete.
查看物化视图
SQL> select count(*) from mv_emp; COUNT(*)---------- 14
数据并没有刷新
先保存物化视图数据的rowid
SQL> create table mv_rowid as select rowid rid from mv_emp;Table created.
我们手工刷新物化视图
SQL> exec DBMS_SNAPSHOT.REFRESH('SCOTT.MV_EMP');PL/SQL procedure successfully completed.查看物化视图
SQL> select count(*) from mv_emp; COUNT(*)---------- 28
查看rowid的交集
SQL> select rowid rid from mv_emp intersect select rid from mv_rowid;no rows selected
可见，物化视图的刷新，是将以前的数据删除，然后执行了全量刷新。
全量刷新比较耗资源，如果基本数据只有很少一部分发生变化，那么应该使用fast方式的快速刷新。要支持快速刷新，那么Oracle一定要知道基表的哪些数据行发生了变化。
由此引入了Materialized log，该日志记录了基表的变化情况。
我们做如下测试：
创建物化视图日志
SQL> CREATE MATERIALIZED VIEW LOG ON EMP;Materialized view log created.
SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------BONUS TABLEDEPT TABLEEMP TABLEMLOG$_EMP TABLERUPD$_EMP TABLESALGRADE TABLE
创建完物化视图日志后，多了两张表MLOG$_EMP,RUPD$_EMP。这两张表就是用来记录emp数据的变化。
接下来我们创建一个快速刷新的物化视图
SQL> create materialized view fmv_emp refresh fast as select * from emp;Materialized view created.SQL> select count(*) from fmv_emp; COUNT(*)---------- 28
我们将emp表中数据删除一部分。
SQL> delete from emp where empno<7000;14 rows deleted.
检查日志表
SQL> col CHANGE_VECTOR$$ for a10SQL> select * from MLOG$_EMP; EMPNO SNAPTIME$$ D O CHANGE_VEC XID$$---------- ------------------- - - ---------- ---------- 6369 4000/01/01 00:00:00 D O 0000 2.2519E+15 6499 4000/01/01 00:00:00 D O 0000 2.2519E+15 6521 4000/01/01 00:00:00 D O 0000 2.2519E+15 6566 4000/01/01 00:00:00 D O 0000 2.2519E+15 6654 4000/01/01 00:00:00 D O 0000 2.2519E+15 6698 4000/01/01 00:00:00 D O 0000 2.2519E+15 6782 4000/01/01 00:00:00 D O 0000 2.2519E+15 6788 4000/01/01 00:00:00 D O 0000 2.2519E+15 6839 4000/01/01 00:00:00 D O 0000 2.2519E+15 6844 4000/01/01 00:00:00 D O 0000 2.2519E+15 6876 4000/01/01 00:00:00 D O 0000 2.2519E+15 EMPNO SNAPTIME$$ D O CHANGE_VEC XID$$---------- ------------------- - - ---------- ---------- 6900 4000/01/01 00:00:00 D O 0000 2.2519E+15 6902 4000/01/01 00:00:00 D O 0000 2.2519E+15 6934 4000/01/01 00:00:00 D O 0000 2.2519E+1514 rows selected.
被删除数据的主键都记录到日志表中，并且操作类型也有记录。
此时，物化视图并没有主动的刷新，因为默认是手工刷新的。
SQL> select count(*) from fmv_emp; COUNT(*)---------- 28
刷新视图
SQL> exec DBMS_SNAPSHOT.REFRESH('FMV_EMP');PL/SQL procedure successfully completed.SQL> select count(*) from fmv_emp; COUNT(*)---------- 14SQL>数据已经同步，并且物化视图日志中的记录也被删除了。如下
SQL> select * from MLOG$_EMP;no rows selected
下面我们看看如何让物化视图自动刷新。
建立物化视图，每个1分钟刷新一次。
SQL> create materialized view auto_refresh_mv_emp refresh fast on demand start with sysdate next sysdate+1/24/60 as select * from emp;Materialized view created.SQL> select count(*) from auto_refresh_mv_emp; COUNT(*)---------- 14SQL>
修改基表内容
SQL> INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO + 1000, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM scott.emp; 14 rows created.SQL> commit;Commit complete.SQL> select count(*) from auto_refresh_mv_emp; COUNT(*)---------- 14
等待一分钟后。
SQL> select count(*) from auto_refresh_mv_emp; COUNT(*)---------- 28SQL>
再看看另一个视图
SQL> select count(*) from fmv_emp; COUNT(*)---------- 14
并没有刷新，如果现在再去刷新这个物化视图。
SQL> select count(*) from mlog$_emp; COUNT(*)---------- 14SQL> exec DBMS_SNAPSHOT.REFRESH('FMV_EMP');PL/SQL procedure successfully completed.SQL> select count(*) from mlog$_emp; COUNT(*)---------- 0SQL> select count(*) from fmv_emp; COUNT(*)---------- 28为什么auto_refresh_mv_emp自动刷新的时候，mlog$_emp表没有被清空呢？当Oracle刷新物化视图时，如果发现还有依赖于相同基表的物化视图没有刷新，它不会清空mlog$_emp表，只会修改SNAPTIME$$字段的值。
那么如果让物化视图commit时即刷新呢？
SQL> create materialized view refresh_at_commit_emp refresh fast on commit as select * from emp;Materialized view created.SQL> select count(*) from refresh_at_commit_emp; COUNT(*)---------- 14
修改基表内容
SQL> INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT EMPNO + 1000, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM scott.emp; 14 rows created.SQL> select count(*) from refresh_at_commit_emp; COUNT(*)---------- 14SQL> commit;Commit complete.SQL> select count(*) from refresh_at_commit_emp; COUNT(*)---------- 28SQL>
可见物化视图随着事务的提交一起刷新了。
物化视图快速刷新有如下限制
The defining query of the materialized view is restricted as follows:
The materialized view must not contain references to non-repeating expressions like
SYSDATEandROWNUM.The materialized view must not contain references to
RAWorLONGRAWdata types.It cannot contain a
SELECTlist subquery.It cannot contain analytic functions (for example,
RANK) in theSELECTclause.It cannot contain a
MODELclause.It cannot contain a
HAVINGclause with a subquery.It cannot contain nested queries that have
ANY,ALL, orNOTEXISTS.It cannot contain a
[START WITH …] CONNECT BYclause.It cannot contain multiple detail tables at different sites.
ONCOMMITmaterialized views cannot have remote detail tables.Nested materialized views must have a join or aggregate.
Materialized join views and materialized aggregate views with a
GROUPBYclause cannot select from an index-organized table.
快速刷新，对物化视图的日志也有限制，我们前面建立的日志，只是最简单的。并不能满足很多快速刷新的要求。
如下物化视图日志，是最完整的日志
SQL> CREATE MATERIALIZED VIEW LOG ON emp WITH SEQUENCE,ROWID(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)INCLUDING NEW VALUES; Materialized view log created.SQL> desc mlog$_emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) M_ROW$$ VARCHAR2(255) SEQUENCE$$ NUMBER SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) XID$$ NUMBERSQL>
查询重写
如果一个sql查询，直接查询基表。但是该查询可以通过物化视图快速得到结果。那么Oracle将改写该查询，使的查询到物化视图中去取数据。
SQL> CREATE MATERIALIZED VIEW agg_emp REFRESH FAST ON DEMAND ENABLE QUERY REWRITEAS SELECT deptno, SUM (sal) sum_sal, -- AVG (sal) avg_sal, COUNT (*) dept_cnt FROM emp GROUP BY deptno;Materialized view created.
SQL> select deptno,sum(sal) from emp group by deptno; DEPTNO SUM(SAL)---------- ---------- 30 18800 20 21750 10 17500Execution Plan----------------------------------------------------------Plan hash value: 2367329769----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 78 | 3 (0)| 00:00:01 || 1 | MAT_VIEW REWRITE ACCESS FULL| AGG_EMP | 3 | 78 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------------------
并且还可以在物化视图上建立索引
SQL> create unique index agg_emp_pk on agg_emp(deptno);Index created.
