一个有关SQL查询优化的问题

[最爱三明治]

这是一个有关子查询的问题，SQL比较长，请大神细细一看便知，请大神帮忙优化，SQL如下：
其中client 1000数据，
ob_tx 100W+数据，
bs_tx 300W+数据，
我简单测试了下，发现最主要是在查询bs_tx的过程中比较慢，其中添加了几个索引如下：
acc_id,client_id,tx_date,direction四个，目前本机测试的查询速度在7S左右，我想优化到2S左右
不知道可行否？7S的速度客户应该是无法接受的，如果在不添加硬件配置的情况下，如何优化SQL或者表结构可以提高SQL的查询速度呢？请大神赐教！！！分不多了，请见谅~

select c.name_abbr,
(select count(*) from ob_tx obtx
left join client_children dcc on obtx.pay_client_id = dcc.child_id
where obtx.pay_acc_id != -1
and obtx.rec_acc_id != -1
and obtx.rec_client_id != 1
and obtx.pay_acc_id != obtx.rec_acc_id
and dcc.client_id = c.id
and obtx.exec_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')) as a1,
(select NVL(ROUND(SUM(obtx.pay_amount)/10000,2),0) from ob_tx obtx
left join client_children dcc on obtx.pay_client_id = dcc.child_id
where obtx.pay_acc_id != -1
and obtx.rec_client_id != 1
--and obtx.rec_acc_id != -1
and obtx.pay_acc_id != obtx.rec_acc_id
and dcc.client_id = c.id
and obtx.exec_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')) as a2,
(select count(*) from ob_tx obtx
left join client_children dcc on obtx.rec_client_id = dcc.child_id
where obtx.pay_acc_id != -1
and obtx.rec_acc_id != -1
and obtx.rec_client_id != 1
and obtx.pay_acc_id != obtx.rec_acc_id
and dcc.client_id = c.id
and obtx.exec_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')) as a3,
(select NVL(ROUND(SUM(obtx.pay_amount)/10000,2),0) from ob_tx obtx
left join client_children dcc on obtx.rec_client_id = dcc.child_id
where obtx.pay_acc_id != -1
and obtx.rec_client_id != 1
--and obtx.rec_acc_id !=-1
and obtx.pay_acc_id != obtx.rec_acc_id
and dcc.client_id = c.id
and obtx.exec_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')) as a4,
(select count(*) from bs_tx bstx
left join client_children dcc on bstx.client_id = dcc.child_id
where bstx.client_id != 1
and bstx.direction = 2
and bstx.tx_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')
and dcc.client_id = c.id ) as b1,
(select NVL(ROUND(SUM(bstx.amount),2),0) from bs_tx bstx
left join client_children dcc on bstx.client_id = dcc.child_id
where bstx.client_id != 1
and bstx.direction = 2
and bstx.tx_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')
and dcc.client_id = c.id ) as b2,
(select count(*) from bs_tx bstx
left join client_children dcc on bstx.client_id = dcc.client_id
where bstx.client_id != 1
and bstx.direction = 1
and bstx.tx_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')
and dcc.client_id = c.id) as b3,
(select NVL(ROUND(SUM(bstx.amount),2),0) from bs_tx bstx
left join client_children dcc on bstx.client_id = dcc.child_id
where bstx.client_id != 1
and bstx.direction = 1
and bstx.tx_date between to_date('20130621','yyyymmdd') and to_date('20130630','yyyymmdd')
and dcc.client_id = c.id ) as b4
from client c
where (c.id = 14 or c.p_id = 14)
and c.id != 1
order by a1 desc

[阿三]

看了你的SQL，几段都差不多，考虑合并吧。 此外，对于嵌套查询，可以利用with 语句。 如： with t as ( select * from aaaa) select * from t;

[snbxp]

!=,这种条件是走不到索引上的

[最爱三明治]

引用 1 楼 zxf_feng 的回复:

看了你的SQL，几段都差不多，考虑合并吧。 此外，对于嵌套查询，可以利用with 语句。 如： with t as ( select * from aaaa) select * from t;

现在是想提高SQL的查询速度，这个有什么办法没有？with as只是对SQL语句进行了一个简化...

[阿三]

with 把具有相同的抽离出来，然后再书写，速度会有所提高，当然要看索引，数据量等。

[love145299]

几个字句的条件都差不多，不用写查询那么多次的吧，大概看了下a1到a4可以用一个sql代替,b1到b4可以用一个sql代替