数据库查询优化

[shang_yi]

select sum(amount) amount,sum(psnpay) psnpay,sum(hppay) hppay,sum(dispay) dispay,sum(subpay) subpay,sum(entpay) entpay,sum(cashpay) cashpay
from (
(
select sum(lit.segmvalue) as amount, 0 as psnpay, 0 as hppay, 0 as dispay, 0 as subpay, 0 as entpay, 0 as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '医疗费总额' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
union all
(
select 0 as amount, sum(lit.segmvalue) as psnpay, 0 as hppay, 0 as dispay, 0 as subpay, 0 as entpay, 0 as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '本次帐户支付' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
union
(select 0 as amount, 0 as psnpay, sum(lit.segmvalue) as hppay, 0 as dispay, 0 as subpay, 0 as entpay, 0 as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '统筹支付金额' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
union
(select 0 as amount, 0 as psnpay, 0 as hppay, sum(lit.segmvalue) as dispay, 0 as subpay, 0 as entpay, 0 as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '大病救助基金支付' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
union
(select 0 as amount, 0 as psnpay, 0 as hppay, 0 as dispay, sum(lit.segmvalue) as subpay, 0 as entpay, 0 as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '公务员补助支付' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
union
(select 0 as amount, 0 as psnpay, 0 as hppay, 0 as dispay, 0 as subpay, sum(lit.segmvalue) as entpay, 0 as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '企业补充基金支付' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
union
(select 0 as amount, 0 as psnpay, 0 as hppay, 0 as dispay, 0 as subpay, 0 as entpay, sum(lit.segmvalue) as cashpay
from zzhp_st st
inner join zzhp_stsplit lit
on st.pk_hpst = lit.pk_hpst
and lit.segmname = '本次现金支付' /*此处应修改前面的代码，将segmcode写入用于后面的条件查询*/
inner join bd_uh_hp hp
on st.pk_hp = hp.pk_hp
and hp.code = '03'
where pk_psn_opt = '0ZXYY1001PSNDOC00680'
and st.code_pvtype = '03'
and nvl(st.flag_canc,'N') = 'N'
)
)



数据量大，怎么优化性能会提升？

[shiyiwan]

SELECT SUM(CASE WHEN lit.segmname = '医疗费总额'  THEN lit.segmvalue END) AS amount
	 , SUM(CASE WHEN lit.segmname = '本次帐户支付'  THEN lit.segmvalue END) AS psnpay
	 , SUM(CASE WHEN lit.segmname = '统筹支付金额'  THEN lit.segmvalue END) AS hppay
	 , SUM(CASE WHEN lit.segmname = '大病救助基金支付'  THEN lit.segmvalue END) AS dispay
	 , SUM(CASE WHEN lit.segmname = '公务员补助支付'  THEN lit.segmvalue END) AS subpay
	 , SUM(CASE WHEN lit.segmname = '企业补充基金支付'  THEN lit.segmvalue END) AS entpay
	 , SUM(CASE WHEN lit.segmname = '本次现金支付'  THEN lit.segmvalue END) AS cashpay
  FROM zzhp_st st
 inner join zzhp_stsplit lit
    on st.pk_hpst = lit.pk_hpst
 inner join bd_uh_hp hp
    on st.pk_hp = hp.pk_hp
   and hp.code = '03'
 where pk_psn_opt = '0ZXYY1001PSNDOC00680'
   and st.code_pvtype = '03'
   and (st.flag_canc = 'N' OR st.flag_canc IS NULL);

[碧水幽幽泉]

楼上正解。

[mayanzs]

以一次扫描替代多次扫描。

[陈灬风]

2楼很对，你如果表格的数据很多，查看相关表格的字段是否有创建索引。