一个有关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;
[解决办法]
!=,这种条件是走不到索引上的
