接到一个需求,为了提高查询效率,需要把一个普通视图转化成物化视图。
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,基于复杂查询的快速刷新的物化视图就创建成功了。
原理:物化视图快速刷新的原理主要是基于物化视图日志,使得每次只刷新物化视图日志中那一小部分数据。
所以我们只要根据物化视图日志,每次只把需要刷新的数据更新到物理表中,更新完毕后清除物化视图日志就行了。