Oracle查询优化：移除OR条件提升ddetl表查询性能

优化思路 #

• 拆分查询合并结果：将OR分隔的两个逻辑分支拆成独立查询，用UNION ALL（确认无重复结果时优先）或UNION合并。每个分支分别对应sr.detl_level != 'Y'和sr.detl_level = 'Y'的场景，让Oracle为每个分支生成针对性的执行计划，避免OR导致的索引失效或全表扫描。
• 预取ddetl最小记录：用WITH子句提前获取v_dsba_id和v_ga_id对应的最小dsba_id, ga_id, seqnbr记录，主查询中通过关联而非IN子句匹配，结合sr.detl_level条件过滤，减少子查询重复执行的开销。
• CASE表达式替换OR：在WHERE子句中用CASE整合逻辑，例如：

  AND CASE 
        WHEN nvl(sr.detl_level,'N') != 'Y' THEN 
            CASE WHEN (dd.dsba_id,dd.ga_id,dd.seqnbr) = (SELECT MIN(dsba_id),MIN(ga_id), MIN(seqnbr) FROM ddetl WHERE dsba_id = v_dsba_id AND ga_id = v_ga_id) THEN 1 ELSE 0 END
        WHEN sr.detl_level = 'Y' THEN
            CASE WHEN dd.dsba_id = v_dsba_id AND dd.ga_id = v_ga_id THEN 1 ELSE 0 END
      END = 1
  

  注意需配合合适索引，确保Oracle能正确解析条件。
• 针对性优化索引：为两个分支的查询条件创建复合索引：针对sr.detl_level = 'Y'的分支，建(dsba_id, ga_id)索引；针对另一分支，建(dsba_id, ga_id, seqnbr)索引，让MIN查询快速命中，避免全表扫描。

原查询（中文翻译） #

SELECT 1,
       rcd.id,
       sr.id ,
       rcd.crit_seq,
       rcd.ev_id,
       null,
       dd.funds_avail_date,
       sr.action_allowed,
       dd.dsba_id,
       decode(sr.detl_level,'Y',dd.seqnbr,null),
       dd.ga_id
  FROM rc_detail   rcd,
       ddetl       dd,
       sr_crit     src,
       srule       sr,
       sr_type     rt
 WHERE rt.id = sr.rule_type
   and sr.id = rcd.std_rl_id
   and src.std_rl_id = sr.id
   and rcd.std_rl_id = src.std_rl_id
   and rcd.crit_seq = src.seqnbr
   and src.crit_type = 'STD'
   and ((nvl(sr.detl_level,'N') != 'Y'
          and (dd.dsba_id,dd.ga_id,dd.seqnbr) in
              (SELECT MIN(dsba_id),MIN(ga_id), MIN(seqnbr)
                  FROM   ddetl
                 WHERE  dsba_id = v_dsba_id
                   AND  ga_id = v_ga_id))
           OR (sr.detl_level = 'Y'
               and dd.dsba_id = v_dsba_id
               and dd.ga_id = v_ga_id))
 and rt.id = 'LOAN-SPEC'
 and nvl(rcd.irs_code,'xxxxx') = 'xxxxx'
 and nvl(rcd.dsrs_code,'xxxxx') = 'xxxxx'
 and nvl(rcd.dsmd_code,'xxxxx') = 'xxxxx'
 and nvl(rcd.sdio_id,dd.sdio_id) = dd.sdio_id
 and nvl(rcd.sdmt_code,dd.sdmt_code) = dd.sdmt_code
 and (nvl(rcd.gaio_qual,dd.gaio_qual) = dd.gaio_qual OR
         dd.gaio_qual is null)
 and (nvl(rcd.gdmt_seqnbr,dd.gdmt_seqnbr) = dd.gdmt_seqnbr OR
         dd.gdmt_seqnbr is null);

需修改的逻辑片段（中文翻译） #

and ((nvl(sr.detl_level,'N') != 'Y'
          and (dd.dsba_id,dd.ga_id,dd.seqnbr) in
              (SELECT MIN(dsba_id),MIN(ga_id), MIN(seqnbr)
                 FROM   ddetl
                WHERE  dsba_id = v_dsba_id
                  AND  ga_id = v_ga_id))
            OR (sr.detl_level = 'Y'
               and dd.dsba_id = v_dsba_id
               and dd.ga_id = v_ga_id))

内容的提问来源于stack exchange，提问作者Aishwarya Venugopal