设为首页 收藏本站
查看: 1821|回复: 0

[经验分享] Oracle 学习之性能优化(十一)物化视图

[复制链接]

尚未签到

发表于 2018-9-9 09:01:40 | 显示全部楼层 |阅读模式
  物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。在数据仓库中,还经常使用查询重写(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 sysdba  
Connected.
  
SQL> grant create materialized view to scott;
  

  
Grant succeeded.
  

  
SQL> conn scott/tiger
  
Connected.
  
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 USERS
  
PCTUSED    0
  
PCTFREE    10
  
INITRANS   2
  
MAXTRANS   255
  
STORAGE    (
  
            INITIAL          64K
  
            NEXT             1M
  
            MAXSIZE          UNLIMITED
  
            MINEXTENTS       1
  
            MAXEXTENTS       UNLIMITED
  
            PCTINCREASE      0
  
            BUFFER_POOL      DEFAULT
  
            FLASH_CACHE      DEFAULT
  
            CELL_FLASH_CACHE DEFAULT
  
           )
  
NOCACHE
  
LOGGING
  
NOCOMPRESS
  
NOPARALLEL
  
BUILD IMMEDIATE
  
USING 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 DEMAND
  
WITH PRIMARY KEY
  
AS
  
/* 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)
  
LOGGING
  
TABLESPACE USERS
  
PCTFREE    10
  
INITRANS   2
  
MAXTRANS   255
  
STORAGE    (
  
            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       TABTYPECLUSTERID
  
------------------------------ ------- ----------
  
BONUS       TABLE
  
DEPT       TABLE
  
EMP       TABLE
  
MLOG$_EMP       TABLE
  
RUPD$_EMP       TABLE
  
SALGRADE       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 col CHANGE_VECTOR$$ for a10  
SQL> 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+15
  

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

  
SQL>
  数据已经同步,并且物化视图日志中的记录也被删除了。如下
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(*)
  
----------
  14
  

  
SQL>
  修改基表内容
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(*)
  
----------
  28
  

  
SQL>
  再看看另一个视图
SQL> select count(*) from fmv_emp;  

  
  COUNT(*)
  
----------
  14
  并没有刷新,如果现在再去刷新这个物化视图。
SQL> select count(*) from mlog$_emp;  

  
  COUNT(*)
  
----------
  14
  

  
SQL> exec DBMS_SNAPSHOT.REFRESH('FMV_EMP');
  

  
PL/SQL procedure successfully completed.
  

  
SQL> select count(*) from mlog$_emp;
  

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

  
SQL> 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(*)
  
----------
  14
  

  
SQL> commit;
  

  
Commit complete.
  

  
SQL>  select count(*) from refresh_at_commit_emp;
  

  
  COUNT(*)
  
----------
  28
  

  
SQL>
  可见物化视图随着事务的提交一起刷新了。
  物化视图快速刷新有如下限制
  The defining query of the materialized view is restricted as follows:

  •   The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
  •   The materialized view must not contain references to RAW or LONG RAW data types.
  •   It cannot contain a SELECT list subquery.
  •   It cannot contain analytic functions (for example, RANK) in the SELECT clause.
  •   It cannot contain a MODEL clause.
  •   It cannot contain a HAVING clause with a subquery.
  •   It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
  •   It cannot contain a [START WITH …] CONNECT BY clause.
  •   It cannot contain multiple detail tables at different sites.
  •   ON COMMIT materialized views cannot have remote detail tables.
  •   Nested materialized views must have a join or aggregate.
  •   Materialized join views and materialized aggregate views with a GROUP BY clause 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$$    NUMBER
  

  
SQL>
  查询重写
  如果一个sql查询,直接查询基表。但是该查询可以通过物化视图快速得到结果。那么Oracle将改写该查询,使的查询到物化视图中去取数据。
SQL> CREATE MATERIALIZED VIEW agg_emp  
   REFRESH FAST ON DEMAND
  
   ENABLE QUERY REWRITE
  
AS
  
     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)
  
---------- ----------
  3018800
  2021750
  1017500
  

  

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



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.iyunv.com/thread-568512-1-1.html 上篇帖子: Oracle 学习之性能优化(十)锁 下篇帖子: Oracle 使用物化视图实现表数据同步
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表