--数据量大概十几w
select SUM(b.BalanceAmt) BalanceAmt,b.StoreId
from OuttblAccountingEntry b
inner join
(select max(AccountingEntryId) AccountingEntryId,StoreId,ReferenceNum
from OuttblAccountingEntry
group by StoreId,ReferenceNum ) a
on a.AccountingEntryId = b.AccountingEntryId
where lower(b.AdjustmentReasonCd) = 'invoice'
GROUP BY b.StoreId
select SUM(b.BalanceAmt) BalanceAmt,b.StoreId
from OuttblAccountingEntry b
inner join
(select max(AccountingEntryId) AccountingEntryId,StoreId,ReferenceNum
from OuttblAccountingEntry
group by StoreId,ReferenceNum ) a
on a.AccountingEntryId = b.AccountingEntryId
where lower(b.AdjustmentReasonCd) = 'invoice'
GROUP BY b.StoreId
我知道如果左边用lower的话会损耗一些性能,但是数据里面不能确定大小写啊
AdjustmentReasonCd 字段也不允许加索引啊 :(
可以建立视图.在视图上加个索引
create view vw1
as
..
转换 AdjustmentReasonCd 为小写
endselect * from vw1 where
where AdjustmentReasonCd = 'invoice'
成 b.AdjustmentReasonCd like '[iI][nN][vV][oO][iI][cC][eE]'好像在table scan 部分消耗好像小了一点。
唉...老外不让偶们随便添加view什么的呀
而且这个table会经常大批量修改,加index也会影响这部分速度呀
痛苦ing...
对b.AdjustmentReasonCd进行lower的处理会降低速度、引起全表扫描。
建议通过程序将b.AdjustmentReasonCd插入的时候就变成小写。