帮忙把这个查询优化到2秒以内

Question

select
count(*) total
from bmsbill
inner join bmsbillinfo on bmsbill.innerno = bmsbillinfo.innerno
right join bmsbuyapprove on bmsbill.institution = bmsbuyapprove.institution
where
bmsbill.billtype = bmsbuyapprove.billtype
and bmsbill.buytype = bmsbuyapprove.buytype
and bmsbill.bundle = bmsbuyapprove.bundle
and bmsbill.buytype in ('01','04')
and bmsbill.institution in (select area_code from missign.mag_area start with area_code = '0450001002' connect by belong_bank = prior area_code)

Answer 1

简单的看了一下

应该是三张表 bmsbill 、 bmsbillinfo 、 bmsbuyapprove 然后你的where的条件更多的用的是 bmsbill 、bmsbuyapprove 。

1、如果 bmsbill 、bmsbuyapprove 两表关联度较高的话，可以先对这两张表的数据进行处理。最后再用 inner join bmsbillinfo on bmsbill.innerno = bmsbillinfo.innerno 方式将两个表进行连接查询。由于不能模拟你的环境，只能给出一点思路。

select
count(*) total
from bmsbill , bmsbuyapprove
where
bmsbill.institution = bmsbuyapprove.institution
bmsbill.billtype = bmsbuyapprove.billtype
and bmsbill.buytype = bmsbuyapprove.buytype
and bmsbill.bundle = bmsbuyapprove.bundle
and bmsbill.buytype in ('01','04')

然后对查询条件进行一个联合索引的操作。

2、将整个的查询结果作为结果，然后 加入你的 bmsbillinfo 的条件即可