sql语句的优化，查询效率低

[qq_41391731]

select sum(nvl(a.ysje, 0) - nvl(a.skje, 0)) ye, a.custcode, a.unitcode
from (select decode(zb.djdl, 'ys', sum(fb.Jfbbje), '0') ysje,
decode(zb.djdl, 'sk', sum(fb.ybye), '0') skje,
bas.custcode,
corp.unitcode
from arap_djzb zb
left join arap_djfb fb
on zb.VOUCHID = fb.VOUCHID
left join bd_cumandoc man
on man.pk_cumandoc = fb.ksbm_cl
left join bd_cubasdoc bas
on bas.pk_cubasdoc = man.pk_cubasdoc
left join bd_corp corp
on corp.pk_corp = zb.dwbm
where bas.custcode = '211200127'
-- substr(bas.custcode, 0, 2) = '11'
and zb.djzt = '2'
and corp.unitcode = '10301'
and nvl(zb.dr, 0) = 0
--and zb.dr = 0
and nvl(fb.dr, 0) = 0
group by bas.custcode, zb.djdl, corp.unitcode) a
group by a.custcode, a.unitcode

关联几张表，查询结果有一条数据，但是效率很低，要十几秒，有没有地方可以优化的大神们

[welyngj]

对于驱动表，给本地谓词列建立索引。
对于被驱动表，给关联列建索引，同时也要考虑给被驱动表的本地谓词列加入到索引中。

[riven2011]

bas.custcode = '211200127'
and corp.unitcode = '10301'
写在相应on的后面

[AHUA1001]

以下的基础上，适当的创建索引。 select sum(nvl(decode(zb.djdl, 'ys', sum(fb.Jfbbje), '0'), 0) - nvl(decode(zb.djdl, 'sk', sum(fb.ybye), '0'), 0)) ye, bas.custcode, corp.unitcode from arap_djzb zb left join arap_djfb fb on zb.VOUCHID = fb.VOUCHID left join bd_cumandoc man on man.pk_cumandoc = fb.ksbm_cl left join bd_cubasdoc bas on bas.pk_cubasdoc = man.pk_cubasdoc left join bd_corp corp on corp.pk_corp = zb.dwbm where bas.custcode = '211200127' -- substr(bas.custcode, 0, 2) = '11' and zb.djzt = '2' and corp.unitcode = '10301' and nvl(zb.dr, 0) = 0 --and zb.dr = 0 and nvl(fb.dr, 0) = 0 group by bas.custcode, zb.djdl, corp.unitcode ;

[lhdz_bj]

引用 楼主 qq_41391731 的回复:

select sum(nvl(a.ysje, 0) - nvl(a.skje, 0)) ye, a.custcode, a.unitcode
from (select decode(zb.djdl, 'ys', sum(fb.Jfbbje), '0') ysje,
decode(zb.djdl, 'sk', sum(fb.ybye), '0') skje,
bas.custcode,
corp.unitcode
from arap_djzb zb
left join arap_djfb fb
on zb.VOUCHID = fb.VOUCHID
left join bd_cumandoc man
on man.pk_cumandoc = fb.ksbm_cl
left join bd_cubasdoc bas
on bas.pk_cubasdoc = man.pk_cubasdoc
left join bd_corp corp
on corp.pk_corp = zb.dwbm
where bas.custcode = '211200127'
-- substr(bas.custcode, 0, 2) = '11'
and zb.djzt = '2'
and corp.unitcode = '10301'
and nvl(zb.dr, 0) = 0
--and zb.dr = 0
and nvl(fb.dr, 0) = 0
group by bas.custcode, zb.djdl, corp.unitcode) a
group by a.custcode, a.unitcode

关联几张表，查询结果有一条数据，但是效率很低，要十几秒，有没有地方可以优化的大神们

1、没有执行计划和数据环境谈SQL优化，没什么依据，实际就是猜；
2、楼主说只有一条数据，这个是分组，也许这个组里数据量很多。