SQL语句的优化

Question

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进行优化一下！！！

Answer 1

没环境 我也只能信口开河了。
第一'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)’ 为什么不直接等于

Answer 2

第一：你刚才所说的第一种方法，我已经尝试过了，但是时间会是双倍的，而且比如说原本查询出的数据有10条，采用union all的方式得到的数据也是双倍的，也就变成20条了！！
第二：a.store_no in(1222)采用这种方式是因为门店是可选的，可以选择多个比如说
a.store_no in(1222,1223,1224)。

Answer 3

一般在操作大型数据的时候最好不要用IN和OR我一般写SQL或者存储过程一般是把那些数据保存在一张表里面，然后INNER JOIN那张表，这样速度会快很多。因为那样会走索引。如果是直接用IN和OR就不会走索引。

Answer 4

用表表变量试一下（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  

Answer 5

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 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'
)
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  
and d.status_flg in ('1','Y')

Answer 6

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  ,c.inv_no
from  pos a,bwhotran b , invoice c 
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')  
and b.com_no=c.com_no(+)
and b.order_primarykey=c.order_primarykey(+)
between '2012-09-01' and '2012-09-01' 
order by a.store_no,b.create_time 

Answer 7

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  ,c.inv_no
from  pos a,bwhotran b , invoice c 
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')  
and b.com_no=c.com_no(+)
and b.order_primarykey=c.order_primarykey(+)
and d.status_flg(+) in ('1','Y')
between '2012-09-01' and '2012-09-01' 
order by a.store_no,b.create_time