今天自助机厂家反馈,小票生成视图需要等待三秒,导致打印出现空白单据。
查看同事书写的视图，相对比较复杂，涉及病人档案、门诊发票、处方、医技、医保结算等十余个数据表，涉及2次行转列和一个json输出。速度其实尚可，但延迟对业务造成影响需要进行调优。
一、
查看原始执行计划
发现E-Rows和Buffers非常高，确实存在很大的性能问题
第一步、发现51行存在隐式转换
filter(NVL("B"."ORDERNO",SYS_OP_C2C("B"."FPHM"))=U'XZYB202309220844369630')
此步骤发生了隐式转换，查看数据源原因是ORDERNO存在大量NULL数据，处理NULL造成了全表扫描。因此考虑增加NVL(ORDERNO,SYS_OP_C2C(FPHM))的函数索引。
处理完后门诊发票表的全表扫描已处理
第二步、病人档案表同样发生全表扫描，隐去无关部分语句。
SELECT B.CZ_ZCHM AS PJDM,
......
(CASE C.BRXB WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END) AS xb,---获取性别
........
listagg(d.fkmc||':'||to_char(e.fkje,'FM999990.00')) as fkxx1---生成收费类型金额数据字符串
FROM 患者档案表 C, 性质字典表 A, 门诊发票表 B
left join (付款方式字典表 d inner join 付款信息表 e on d.fkfs = e.fkfs) on B.mzxh = e.mzxh
WHERE C.BRID = B.BRID AND A.BRXZ = B.BRXZ
group BY B.CZ_ZCHM,B.FYZH,B.BRXM,C.BRXB,B.SFRQ,B.brxz,B.FPHM,B.ZJJE,B.JGID,B.CZGH,B.Orderno,c.brid,b.sfrq
因为生成收费类型金额数据字符串使用行转列，查询使用了多个字段group by。发现对患者档案表的查询，仅为了获取性别。考虑改写sql，使用子查询获取性别字段。
同理清理部分重复查询或者不合理子查询。
第三步、收费类型金额数据字符串使用行转列时使用的大量group对资源消耗较大，考虑进一步改写sql。
a表：
SELECT B.CZ_ZCHM AS PJDM,
......
(SELECT DECODE(brxb,'1','男',2,'女') FROM ms_brda WHERE a.brid = brid) AS xb
FROM 性质字典表 A, 门诊发票表 B
left join (付款方式字典表 d inner join 付款信息表 e on d.fkfs = e.fkfs) on B.mzxh = e.mzxh
WHERE A.BRXZ = B.BRXZ
b表：
select mzxh,listagg(b.fkmc || ':' || to_char(a.fkje, 'FM999990.00')) AS fkxx1
from 付款信息 a, 付款方式字典表 b
where a.fkfs = b.fkfs
group by mzxh
再对A，B表进行关联。
调整后观察执行计划，速度和资源消耗有了明显的改观,查询速度降低到0.1秒以内。总体效率提升约1000倍以上。
