SQL语句的优化
select c.store_no,c.n_card_phy_id,c.n_txn_amt,c.cday,c.msg_type, c.sub_type,c.ctime, d.inv_no
from
(select a.com_no,a.store_no,b.order_primarykey,b.n_card_phy_id,b.n_txn_amt,to_char(b.create_time,'yyyy-MM-dd') cday,
b.msg_type, b.sub_type,to_char(b.create_time,'HH24:mi:ss') ctime
from pos a,bwhotran b
where b.com_no='1001'
and a.com_no = b.com_no
and a.pos_no=b.pos_no
and a.store_no in(1222)
and to_char(b.create_time,'yyyy-MM-dd')
between '2012-09-01' and '2012-09-01'
order by a.store_no,b.create_time) c
left join invoice d on c.com_no = d.com_no
and c.order_primarykey = d.order_primarykey
and d.status_flg in ('1','Y')
invoice 这张表有很大的数据，其d.com_no，d.order_primarykey
d.status_flg 均是索引！！现在运行这条语句需要5-6min，在程序里面显示出这张报表需要的时间要10分钟左右，
请问有什么方法对该SQL进行优化一下！！！
[解决办法]
没环境 我也只能信口开河了。
第一'and d.status_flg in ('1','Y')‘ 改成
select c.store_no,c.n_card_phy_id,c.n_txn_amt,c.cday,c.msg_type, c.sub_type,c.ctime, d.inv_no
from
(select a.com_no,a.store_no,b.order_primarykey,b.n_card_phy_id,b.n_txn_amt,to_char(b.create_time,'yyyy-MM-dd') cday,
b.msg_type, b.sub_type,to_char(b.create_time,'HH24:mi:ss') ctime
from pos a,bwhotran b
where b.com_no='1001'
and a.com_no = b.com_no
and a.pos_no=b.pos_no
and a.store_no in(1222)
and to_char(b.create_time,'yyyy-MM-dd')
between '2012-09-01' and '2012-09-01'
order by a.store_no,b.create_time) c
left join invoice d on c.com_no = d.com_no
and c.order_primarykey = d.order_primarykey
and d.status_flg =‘1’
union all
select c.store_no,c.n_card_phy_id,c.n_txn_amt,c.cday,c.msg_type, c.sub_type,c.ctime, d.inv_no
from
(select a.com_no,a.store_no,b.order_primarykey,b.n_card_phy_id,b.n_txn_amt,to_char(b.create_time,'yyyy-MM-dd') cday,
b.msg_type, b.sub_type,to_char(b.create_time,'HH24:mi:ss') ctime
from pos a,bwhotran b
where b.com_no='1001'
and a.com_no = b.com_no
and a.pos_no=b.pos_no
and a.store_no in(1222)
and to_char(b.create_time,'yyyy-MM-dd')
between '2012-09-01' and '2012-09-01'
order by a.store_no,b.create_time) c
left join invoice d on c.com_no = d.com_no
and c.order_primarykey = d.order_primarykey
and d.status_flg =‘Y’
第二 ‘and a.store_no in(1222)’ 为什么不直接等于
[解决办法]
执行计划发上来看看
[解决办法]
一般在操作大型数据的时候最好不要用IN和OR我一般写SQL或者存储过程一般是把那些数据保存在一张表里面，然后INNER JOIN那张表，这样速度会快很多。因为那样会走索引。如果是直接用IN和OR就不会走索引。
[解决办法]
用表表变量试一下（SQL SERVER里面这样叫，刚刚开始学ORACLE不知道是否一样），把子查询拿出来
WITH AA AS
(select a.com_no,a.store_no,b.order_primarykey,b.n_card_phy_id,b.n_txn_amt,to_char(b.create_time,'yyyy-MM-dd') cday,
b.msg_type, b.sub_type,to_char(b.create_time,'HH24:mi:ss') ctime
from pos a,bwhotran b
where a.com_no = b.com_no
and a.pos_no=b.pos_no
and a.store_no in(1222)
AND b.com_no='1001'
and to_char(b.create_time,'yyyy-MM-dd')
between '2012-09-01' and '2012-09-01'
)
select c.store_no,c.n_card_phy_id,c.n_txn_amt,c.cday,c.msg_type, c.sub_type,c.ctime, d.inv_no
from AA C
left join invoice d on c.com_no = d.com_no
and c.order_primarykey = d.order_primarykey
