暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Materialized view物化视图的一个简单应用场景

Materialized view物化视图的一个简单应用场景

一森咖记 2019-12-27
2116

【此为"一森咖记"公众号——第101篇文章】

本文阅读预计10分钟

【引言】

最近有同事问了一SQL问题，涉及到自创建的存储过程：GetDistance，以及函数MIN的运算，执行时间太长，能不能优化下。其中有个中间表C查询时长在近3min（见下图）。考虑到业务需求，需要进行全表扫，还有什么优化空间？
耗时时间较长的中间表C
上述SQL，
a表为飞机实际飞行高度FLIGHT_HEIGHT_ACTUAL；
b表FLIGHT_HEIGHT_PLAN为计划飞行高度表；
函数GetDistance是基于地球两点间的经纬度求得之间的曲面距离。
A表和B表为记录数据，数据基本不会发生变化。
 
基于上述业务特点，很自然想到的一个的一个解决方案就是，把中间表c创建为物化视图。让耗时长的c表计算过程提前进行计算。
 
这里先简单介绍下物化视图。
 
创建物化视图的过程如下：
普通视图是虚拟表，应用的局限性大，任何对视图的查询， Oracle 都实际上转换为视图SQL语句的查询，对整体SQL查询性能没有实质上的提高。普通视图的本质其实就是基于业务要求对基表做了整合、封装。
 
不同于普通视图只有在使用的时候才去读取数据，物化视图则是一张实际存在的表，占有磁盘存储，是预先计算并保存表连接或者聚集等比较耗时操作的结果，带来查询结果的提升速度。特别适合抽取大数据量表的某些信息和计算，使用于OLAP类型的抽取和分析。
 
这里先看下创建物化视图的语句
create materialized view [view_name]
build [immediate|deferred]
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
]
 [enable | disable] query rewrite
as
{创建物化视图用的查询语句}
 
1.build [immediate|deferred]
创建方式（BuildMethods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。
BUILD IMMEDIATE是在创建物化视图时就生成数据。
BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。
 
2.refresh [fast|complete|force] 视图刷新的方式：
fast：增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on （主表名）。
complete：全部刷新。相当于重新执行一次创建视图的查询语句。
force：这是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。
 
3.MV数据刷新的时间：
on demand：在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）
 
on commit：当主表中有数据提交时，立即刷新MV中的数据；
start ……：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；
 
4.查询重写（QueryRewrite)
包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。
分别指出创建的物化视图是否支持查询重写。QUERY REWRITE查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。
 
接下来，我们看下C表如何改写为物化视图，步骤如下：

1. 创建物化视图
    create materialized view FLIGHT_HEIGHT_MIN_DIS
    build immediate refresh complete on demand
    as(
    SELECT a.key,
                     B.ROUTE_ID,
                     MIN (GetDistance (a.LATITUDE,
                                       a.LONGITUDE,
                                       b.LATITUDE,
                                       b.LONGITUDE))
                        AS min_distance
                FROM FLIGHT_HEIGHT_ACTUAL a, FLIGHT_HEIGHT_PLAN b
               WHERE a.key = b.key
            GROUP BY a.key, b.ROUTE_ID);
     考虑到a表和b表增删改频度很少，所以更新模式采用demand，此模式下修改基表，物化视图是不会跟着改变；只能手动DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。
     
    注意：这里如使用on commit模式。一旦基表有了事务提交COMMIT，则立刻更新物化视图，使数据和基表一致。缺点是对基表的DML操作有影响，对基表的DML和对物化视图的刷新是在同一个事务下。如果物化视图刷新异常，主表会无法提交事务。
     
    “on commit”时需注意如下几点：
    1. 主表和物化视图须在同一个数据库中；
    2. 主表提交时物化视图也要提交，在OLTP系统中会影响一部分性能。如果物化视图更新出错，主表也无法提交事务；
    3. 无法在基表上执行分布式事务；
    4. 不支持含有对象类型或Oracle补充支持类型的物化视图；
     
    如何删除物化视图
    drop materialized view {物化视图名称};
     
    在demand模式下，基表数据发生变化，物化视图是不会跟着改变，那不行啊，怎么搞？
     
    两种方式：
    手动DBMS_MVIEW.REFRESH方法来进行刷新；
    也可通过JOB定时进行刷新物化视图。
     
    方式1：手动刷新：
    如何手动刷新？
    BEGIN
    DBMS_MVIEW.REFRESH (
    list => 'FLIGHT_HEIGHT_MIN_DIS',
    Method =>'C',
    refresh_after_errors => True);
    END;
     
    也可以直接写物化视图的名称
    begin
     dbms_mview.refresh('FLIGHT_HEIGHT_MIN_DIS');
    end;
    /
     
    FLIGHT_HEIGHT_MIN_DIS是刚建立的物化视图。
     
    方式2：使用job去刷新物化视图
    begin
    dbms_scheduler.create_job(
      job_name => 'timed_job', --job名
      job_type => 'STORED_PROCEDURE', --job类型
      job_action => 'pc_mv', --存储过程名
      start_date => sysdate, --开始执行时间
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=10', --每10分钟执行存储过程pc_mv
      comments => '定期刷新物化视图', --注释
      auto_drop => false, --job禁用后是否自动删除
      enabled => true);
    end;
    /
     
    物化视图FLIGHT_HEIGHT_MIN_DIS创建好后，
    接着创建一个存储过程PROC_FLIGHT_HEIGHT_MIN_DIS来刷新物化视图
     
    1. 创建存储过程
    create or replace procedure PROC_FLIGHT_HEIGHT_MIN_DIS is
    begin
     dbms_mview.refresh('my_mv_cd');
    end PROC_FLIGHT_HEIGHT_MIN_DIS;
    /
     
    2. 创建一个schedule job来trigger刚定义的存储过程
    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
       job_name           =>  'timed_job',
       job_type           =>  'STORED_PROCEDURE', --指定执行的是存储过程
       job_action         =>  'FLIGHT_HEIGHT_MIN_DIS', --对应的存储过程名称
       repeat_interval    =>  'FREQ=MINUTELY; INTERVAL=1', --每间隔1分钟trigger一次
       enabled            =>  true
       );
    END;
    /
     
    如下sql可查询创建的schedule job
    --查询
    select * from ALL_SCHEDULER_JOBS;
    select * from USER_SCHEDULER_JOBS;
     
    删除schedule job
    BEGIN
      DBMS_SCHEDULER.DROP_JOB(
         job_name => 'timed_job'
      );
    END;
    /
     
    最后，总结什么情况下不适合使用物化视图
    1.基表有频繁的DML操作；on commit模式下物化视图刷新时需要额外开销，大大增加基表DML操作的成本和；
     
    2.多表连接的物化视图仅适合 refresh complete；定时刷新，且刷新时间不能小于30min，否则弊大于利（complete是删除原有数据重新连接多表数据）；
     
    【总结】
    1. 本文讲述了通过物化视图提前计算中间结果的方式，来缩短对sql的响应时间；
    2. 本文重点讲述了refresh complete on demand的模式，后续将介绍refresh fast模式；
     
    To be continued.
     
    【参考】
    http：//blog.itpub.net/29047826/viewspace-1575568/
    【参考】
    https：//docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235
    【参考】
    https：//mp.weixin.qq.com/s/5Lg8cSEb3R5WkQwVF9xfPQ
    【参考】
    https：//blog.csdn.net/cckevincyh/article/details/88982217
    【参考】
    https：//blog.csdn.net/zq9017197/article/details/6985109
     


    下是个人微信公众号“一森咖记”，欢迎关注

       

    近期热文
    你可能也会对以下话题感兴趣。点击链接就可以查看。


    最后修改时间：2019-12-28 11:20:20
    文章转载自一森咖记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

    评论