SQL条件查询加载速度过慢的可行优化方案咨询

你之前将WHERE子句的AND替换为IN后返回空，是逻辑改写错误导致的：原过滤逻辑为「类型为BILL且状态为未清 或 类型为BILLPMT 或 类型为INV且状态为未清」，如果错误地将AND条件直接合并到IN子句中，BILLPMT类型没有匹配的状态条件，自然无符合条件的行返回。正确的IN写法为：

WHERE (t.AbbrevType, BUILTIN.DF(t.Status)) IN (('BILL','Bill : Open'),('INV','Invoice : Open')) OR t.AbbrevType = 'BILLPMT'

以下是可落地的查询优化方案：

1. 修复低效的日期比较逻辑 #

你当前用TO_CHAR转换日期为字符串再比较的写法无法命中日期索引，还存在逻辑漏洞，直接替换为日期截断比较即可：
将所有重复的年月比较逻辑替换为TRUNC(t.TranDate, 'MM') >= TRUNC(ap.startdate, 'MM')，无需转换字符串，执行效率提升明显，同时避免逻辑错误。

2. 新增覆盖索引 #

在对应表上创建联合覆盖索引，避免查询时回表扫描，大幅降低IO开销：

• Transaction表索引：(AbbrevType, Status, ID, PostingPeriod, Entity, Currency, Tranid, TranDate, Exchangerate, ForeignTotal)，覆盖WHERE过滤、JOIN关联和SELECT查询的所有字段
• TransactionLine表索引：(Transaction, subsidiary, MainLine)，覆盖JOIN关联和WHERE过滤条件
• CurrencyRate表索引：(BaseCurrency, TransactionCurrency, EffectiveDate, Exchangerate)，覆盖JOIN关联和取值字段
• consolidatedexchangerate表索引：(postingperiod, fromsubsidiary, tosubsidiary, averagerate)，覆盖JOIN关联和取值字段

3. 提前过滤数据集 #

将过滤逻辑前置到子查询中，先筛出Transaction表的符合条件的少量数据，再关联其他表，减少JOIN运算的数据量：

FROM (
  SELECT 
    ID, PostingPeriod, Entity, Currency, Tranid, TranDate, Exchangerate, ForeignTotal, AbbrevType, Status
  FROM Transaction 
  WHERE AbbrevType IN ('BILL','BILLPMT','INV') 
  AND (AbbrevType = 'BILLPMT' OR BUILTIN.DF(Status) IN ('Bill : Open','Invoice : Open'))
) t

4. 删除无效关联 #

你当前查询中LEFT JOIN CurrencyRate cr3的关联结果没有在SELECT、WHERE等任何逻辑中使用，直接删除该关联语句即可，减少不必要的表关联开销。

5. 消除重复计算 #

你当前查询中源汇率的CASE判断逻辑重复出现了3次，可以用CTE或者子查询先计算出该值，后续直接引用，降低数据库重复计算的开销。

内容的提问来源于stack exchange，提问作者NooB Gamer