物化视图

示例：

福绵网站建设公司成都创新互联公司,福绵网站设计制作，有大型网站制作公司丰富经验。已为福绵超过千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设要多少钱，请找那个售后服务好的福绵做网站的公司定做！

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);