物化视图

示例：

DROP MATERIALIZED VIEW MV_YYJL;

CREATE MATERIALIZED VIEW MV_YYJL
USING NO INDEX
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT T.rowid t_rowid,jz.rowid yy_rowid, T.ZXID,
JZ.XM,
JZ.XB||” as xb,
JZ.XBMC,
JZ.CSRQ,
JZ.SFZHM AS SFZH,
JZ.YKTH,
JZ.KLX,
JZ.DABH,
T.YWJGMC YYMC,
T.YWMC,
T.KSRQ AS YYKSRQ,
T.TZRQ AS YYTZRQ,
T.JLLYXTMC,
T.JLLYXTBH,
T.YWJGDM,
T.YWJGMC,
‘门诊诊疗’ AS XXLY,
‘1’ as xxlydm
FROM MZ_YYJL T,
MZ_JZJL JZ
WHERE
T.MZLSH = JZ.MZLSH
AND T.JLLYJGBH = JZ.JLLYJGBH
AND T.JLLYXTBH = JZ.JLLYXTBH
union all
SELECT t.rowid as t_rowid,t2.rowid as yy_rowid, T.ZXID,
t2.BZXM XM,
t2.BZXBDM XB,
t2.BZXBMC XBMC,
t2.BZCSRQ CSRQ,
DECODE(t2.BZSFBSLB,
’01’,
t2.BZSFBSHM,
‘1’,
t2.BZSFBSHM,
”) SFZH,
YKTH,
KLX,
” as DABH,
t.ywjgmc YYMC,
BZYWMC YWMC,
BZYYKSRQSJ YYKSRQ,
BZYYTZRQSJ YYTZRQ,
t.JLLYXTMC,
t.JLLYXTBH,
t.YWJGDM,
t.YWJGMC,
‘电子病历’ AS XXLY,
‘3’ as xxlydm
FROM BZ_ZYYYJL t,
BZ_ZYBASY t2
where t.BZJLJQM = t2.BZJLJQM
AND t.JLLYJGBH = t2.JLLYJGBH
AND t.JLLYXTBH = t2.JLLYXTBH
union all
SELECT t.rowid as t_rowid,zz.rowid as yy_rowid,T.ZXID,
ZZ.XM,
ZZ.XB||” as xb,
ZZ.XBMC,
ZZ.CSRQ,
ZZ.SFZHM AS SFZH,
ZZ.YKTH,
ZZ.KLX,
” AS DABH,
T.Ywjgmc AS YYMC,
T.YWMC,
T.YYKSRQ,
T.YYTZRQ,
T.JLLYXTMC,
T.JLLYXTBH,
T.YWJGDM,
T.YWJGMC,
‘住院诊疗’ AS XXLY,
‘2’ as xxlydm
FROM ZZ_ZYYYJL T , ZZ_ZYJZJL ZZ
where T.ZYLSH = ZZ.ZYLSH
AND T.JLLYXTBH = ZZ.JLLYXTBH
AND T.JLLYJGBH = ZZ.JLLYJGBH;

create unique index idx_mv_yyjl_zxid on MV_YYJL (zxid,XXLYDM);

create index idx_mv_yyjl_hcm on mv_yyjl(ywjgdm,sfzh,xm,xb,csrq,xxlydm,yymc);