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不知道是否一样），把子查询拿出来

SQL code

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