自己创建包含复杂查询的快速刷新的物化视图（物化视图日志的另类用法）

最新推荐文章于 2024-01-29 13:53:03 发布

sereya01

最新推荐文章于 2024-01-29 13:53:03 发布

阅读量1.7k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/sereya01/article/details/39370925

版权

oracle 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

接到一个需求，为了提高查询效率，需要把一个普通视图转化成物化视图。

    SELECT
    t.studentcode,
    c.examcode,
    MAX(t.readtime) readtime,
    decode(c.traintype,'xyyw01',MAX(t.coachcode),a.coachcode) coachcode,
    SUM(ROUND(nvl2(d.mnzsbl,least(t.totaltime*d.mnzsbl,d.mnzszdz),least(t.totaltime,d.period)))) totaltime,
    SUM(t.totaltenor) totaltenor,
    SUM(T.Totalmoney) Totalmoney,
    '1' ISVALID,
    MIN(T.Firsttime) Firsttime,
    MIN(T.source) source,
    case when SUM(ROUND(nvl2(d.mnzsbl,least(t.totaltime*d.mnzsbl,d.mnzszdz),least(t.totaltime,d.period)))) >= MAX(e.period) then '1' else '0' end TIMESTANDARD,
    case when SUM(t.totaltenor) >= MAX(nvl(e.mileage,0)) then '1' else '0' end TENORSTANDARD
    FROM mv_studentexaminfo t,
        gen_studentinfo a,            
        teach_subexamsubjectinfo b,   
        teach_examsubjectinfo c,      
        teach_examperiod d,           
        teach_mainexamperiod e        
    WHERE t.studentcode = a.studentcode
    AND t.examcode = b.examcode
    AND b.maincode = c.examcode
    AND t.examcode = d.examcode
    AND a.DRIVECARTYPE = d.drivecartype
    AND c.examcode = e.examcode
    AND a.DRIVECARTYPE = e.drivecartype
    GROUP BY t.studentcode,c.examcode,c.traintype,a.coachcode

其中mv_studentexaminfo大表，其他的都是小表。
而且业务要求为了提高效率只有mv_studentexaminfo表数据更新时才更新到物化视图中，其他小表数据更新时不更新物化视图中原来的数据。

我先按照普通的方法建物化视图

--1.赋值权限，用SYS用户执行
GRANT CREATE MATERIALIZED VIEW TO DFJ;

--2.建立物化视图日志
create materialized view log on MV_STUDENTEXAMINFO with rowid,sequence
(
STUDENTCODE,
EXAMCODE,
READTIME,
COACHCODE,
TOTALTIME,
TOTALTENOR,
TOTALMONEY,
ISVALID,
FIRSTTIME,
"SOURCE"
) including new values;

<pre class="sql" name="code">--3.建立物化视图

CREATE materialized VIEW MV_STUDENTEXAMSUMMARYINFOREFRESH FAST ON DEMAND 自定义刷新时间 --第一次刷新时间 START WITH SYSDATE --刷新时间间隔。 NEXT TRUNC(sysdate,'mi') +1/(24 * 60)WITH rowid --USING DEFAULT LOCAL ROLLBACKSEGMENT DISABLE QUERY REWRITEAS SELECTA.STUDENTCODE STUDENTCODE,DKM.EXAMCODE EXAMCODE,--DKM.TRAINTYPE,MIN(A.READTIME) READTIME,DECODE(DKM.TRAINTYPE,'xyyw02',STU.COACHCODE,'xyyw03',STU.COACHCODE,MAX(A.COACHCODE)) COACHCODE, --如果是科目二或科目三就取学员当前绑定的教练SUM(A.TOTALTIME) TOTALTIME,SUM(A.TOTALTENOR) TOTALTENOR,SUM(A.TOTALMONEY) TOTALMONEY,'1' ISVALID,MIN(A.FIRSTTIME) FIRSTTIME,MIN(A.SOURCE) "SOURCE"FROM MV_STUDENTEXAMINFO A, --小科目汇总表GEN_STUDENTINFO STU, --学员表TEACH_SUBEXAMSUBJECTINFO XKM, --小科目表TEACH_EXAMSUBJECTINFO DKM --大科目表WHERE A.STUDENTCODE = STU.STUDENTCODE(+)AND A.EXAMCODE = XKM.BOSID AND XKM.MAINCODE = DKM.EXAMCODEGROUP BY A.STUDENTCODE,DKM.EXAMCODE,DKM.TRAINTYPE,STU.COACHCODE;

执行创建语句果然报错：不能从复杂查询中获得快速查询。

错误提示：
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

思考：既然Oracle自己的物化视图无法满足现有的业务需求，那么我就自己做一个快速刷新的“物化视图”。

从前面的步奏2开始：

--2.建立物化视图日志
create materialized view log on MV_STUDENTEXAMINFO with rowid,sequence
(
STUDENTCODE,
EXAMCODE,
READTIME,
COACHCODE,
TOTALTIME,
TOTALTENOR,
TOTALMONEY,
ISVALID,
FIRSTTIME,
"SOURCE"
) including new values;

-- 3.自己建立一张保存结果的物理表
create table GEN_STUDENTEXAMSUMMARYINFO
(
  studentcode   VARCHAR2(10) not null,
  examcode      VARCHAR2(10) not null,
  readtime      VARCHAR2(17),
  coachcode     VARCHAR2(10),
  totaltime     NUMBER(8,2),
  totaltenor    NUMBER(15,2),
  totalmoney    NUMBER(8,2),
  isvalid       VARCHAR2(1),
  firsttime     VARCHAR2(17),
  source        VARCHAR2(1),
  timestandard  VARCHAR2(1),
  tenorstandard VARCHAR2(1)
)

--4.自己建立一个刷新“自建物化视图”的存储过程。
CREATE OR REPLACE PROCEDURE refresh_ExamSummary IS

  nm_sequence NUMBER := 0;
  nm_xid      NUMBER := 0;
BEGIN
  --取得当前日志中最大的XID和SEQUENCE
  SELECT MAX(T.xid$$),MAX(T.Sequence$$)
  INTO nm_xid,nm_sequence
  FROM mlog$_mv_studentexaminfo T;

  --更新大科目汇总表
  MERGE INTO gen_studentexamsummaryinfo p 
  USING 
  (
    SELECT
    t.studentcode,
    c.examcode,
    MAX(t.readtime) readtime,
    decode(c.traintype,'xyyw01',MAX(t.coachcode),a.coachcode) coachcode,
    SUM(ROUND(nvl2(d.mnzsbl,least(t.totaltime*d.mnzsbl,d.mnzszdz),least(t.totaltime,d.period)))) totaltime,
    SUM(t.totaltenor) totaltenor,
    SUM(T.Totalmoney) Totalmoney,
    '1' ISVALID,
    MIN(T.Firsttime) Firsttime,
    MIN(T.source) source,
    case when SUM(ROUND(nvl2(d.mnzsbl,least(t.totaltime*d.mnzsbl,d.mnzszdz),least(t.totaltime,d.period)))) >= MAX(e.period) then '1' else '0' end TIMESTANDARD,
    case when SUM(t.totaltenor) >= MAX(nvl(e.mileage,0)) then '1' else '0' end TENORSTANDARD
    FROM mv_studentexaminfo t,
        gen_studentinfo a,            --学员表
        teach_subexamsubjectinfo b,   --小科目表
        teach_examsubjectinfo c,      --大科目表
        teach_examperiod d,           --小科目定额表
        teach_mainexamperiod e        --大科目定额表
    WHERE t.studentcode = a.studentcode
    AND t.examcode = b.examcode
    AND b.maincode = c.examcode
    AND t.examcode = d.examcode
    AND a.DRIVECARTYPE = d.drivecartype
    AND c.examcode = e.examcode
    AND a.DRIVECARTYPE = e.drivecartype
    AND t.studentcode in (
      SELECT
      t1.studentcode
      FROM MLOG$_MV_STUDENTEXAMINFO T1
      WHERE t1.XID$$ <= nm_xid
        AND t1.Sequence$$ <= nm_sequence
      GROUP BY t1.studentcode
    )
    GROUP BY t.studentcode,c.examcode,c.traintype,a.coachcode
  ) np 
  ON (p.studentcode = np.studentcode AND p.examcode = np.examcode) 
  WHEN MATCHED THEN 
  UPDATE 
  SET 
  p.readtime = np.readtime,
  p.coachcode = np.coachcode,
  p.totaltime = np.totaltime, 
  p.totaltenor = np.totaltenor,
  p.Totalmoney = np.Totalmoney,
  p.firsttime = np.Firsttime
  WHEN NOT MATCHED THEN
  INSERT (studentcode, examcode, readtime, coachcode, totaltime, totaltenor, Totalmoney, ISVALID, Firsttime, source)
  VALUES (np.studentcode,np.examcode,np.readtime,np.coachcode,np.totaltime,np.totaltenor,np.Totalmoney,np.ISVALID,np.Firsttime,np.source)
  ; 

  --删除已汇总的日志
  DELETE mlog$_mv_studentexaminfo T
  WHERE T.XID$$ <= nm_xid
  AND T.Sequence$$ <= nm_sequence;

  COMMIT;
/*EXCEPTION
  WHEN OTHERS THEN
    出错日志记录
     */
END refresh_ExamSummary;

--5.创建一个job来定时执行存储过程
  sys.dbms_job.submit(job => :job,
                      what => 'refresh_ExamSummary;',
                      interval => 'TRUNC(sysdate,''mi'') +1/(24 * 60)');

OK,基于复杂查询的快速刷新的物化视图就创建成功了。

原理：物化视图快速刷新的原理主要是基于物化视图日志，使得每次只刷新物化视图日志中那一小部分数据。

所以我们只要根据物化视图日志，每次只把需要刷新的数据更新到物理表中，更新完毕后清除物化视图日志就行了。