包含日期处理逻辑的SQL查询加载缓慢问题及优化咨询

• 修正WHERE条件的逻辑运算符优先级错误
  原查询中t.AbbrevType LIKE ('%INV') OR t.AbbrevType LIKE ('%BILL') AND tl.subsidiary IN (...)的逻辑存在括号缺失问题，由于AND优先级高于OR，实际执行时会返回大量不符合子公司筛选要求的INV类型数据，多扫描数倍的无效数据。修正方案为将两个类型筛选条件用括号包裹：

  (t.AbbrevType LIKE '%INV' OR t.AbbrevType LIKE '%BILL') AND tl.subsidiary IN (...)
  

  另外后续CASE判断中仅用到INV/BILL两个固定枚举值，可直接用等值判断IN ('INV', 'BILL')替代左模糊LIKE，避免左模糊无法命中索引的问题。
• 消除重复计算逻辑
  用于计算BeginningFxRate的CASE表达式在查询中重复出现了3次，可通过CTE或子查询提前计算一次该字段值，后续直接复用结果，减少重复运算开销。
• 修复日期比较的索引失效问题
  原有逻辑通过TO_CHAR将日期转为字符串后再比较年份、月份，会导致TranDate、ap.startdate字段的索引失效。可替换为原生日期函数做比较，例如：

  -- 原字符串比较逻辑
  TO_CHAR(t.TranDate, 'YYYY') >= TO_CHAR(ap.startdate, 'YYYY') AND TO_CHAR(t.TranDate, 'MM') >= TO_CHAR(ap.startdate, 'MM')
  -- 替换为日期原生比较
  t.TranDate >= DATE_TRUNC('month', ap.startdate)
  

  调整后可直接命中日期字段的索引，大幅降低范围查询的耗时。
• 删除冗余过滤条件
  你在TransactionLine的JOIN条件中已经加了2019年5月的日期过滤，后续WHERE子句又新增了2021年7月的日期过滤，两次日期范围不一致，会导致数据库先扫描2019年的所有符合条件数据，再过滤出2021年的部分，白白浪费IO资源。可直接删除JOIN条件中的冗余日期过滤逻辑，统一在WHERE子句中做日期筛选。
• 简化嵌套分页逻辑
  目前使用三层嵌套实现分页，数据需要在多层查询中传递，额外增加了内存开销。如果是Oracle 12c及以上版本，可直接用FETCH FIRST 5000 ROWS ONLY语法替代ROWNUM嵌套分页；低版本也可简化为两层嵌套即可实现相同分页效果。
• 优化关联表的查询效率
  1. 关联AccountingPeriod时的条件ap.id BETWEEN 298 AND 298可直接简化为ap.id = 298，如果业务允许可提前查询出该id对应的startdate、enddate作为常量写入查询，减少一次表关联开销。
  2. 给关联字段添加联合索引，避免回表查询：
     • Transaction表添加联合索引：(AbbrevType, TranDate, CloseDate, Currency, Entity, PostingPeriod)
     • TransactionLine表添加联合索引：(Transaction, MainLine, subsidiary)
     • CurrencyRate表添加联合索引：(BaseCurrency, TransactionCurrency, EffectiveDate)

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