如何将每日执行80万次的低效DB2子查询改写为连接查询？

原查询通过IN子查询筛选每组最新活动时间的记录，我们可以将其改写为JOIN关联派生表的形式，逻辑完全等价，同时可能更利于DB2优化器生成高效执行计划：

SELECT 1
INTO V_SELECT_ONE
FROM FB_POLICY p
JOIN (
    SELECT FB_POL_POLICY_NO, FB_POL_GROUP_NO, MAX(FB_POL_ACTIVITY_TS) AS MAX_ACTIVITY_TS
    FROM FB_POLICY
    WHERE FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = IN_MEMBER_NUMBER
      AND NOT (SUBSTR(FB_POL_LOB,1,4) IN ('FBHP','LIFE') AND FB_POL_EFFECTIVE_DT <= CURRENT DATE)
    GROUP BY FB_POL_POLICY_NO, FB_POL_GROUP_NO
) p_max ON p.FB_POL_POLICY_NO = p_max.FB_POL_POLICY_NO
       AND p.FB_POL_GROUP_NO = p_max.FB_POL_GROUP_NO
       AND p.FB_POL_ACTIVITY_TS = p_max.MAX_ACTIVITY_TS
WHERE p.FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5) = IN_MEMBER_NUMBER
  AND p.FB_POL_STATUS_CD = 'A'
FETCH FIRST 1 ROWS ONLY;

改写说明 #

• 将原IN子查询转换为派生表p_max，通过JOIN与主表关联，匹配每组的政策号、组号以及最新活动时间。
• 主表的过滤条件（成员号匹配、状态为'A'）保留在WHERE子句中，确保只返回符合业务要求的有效记录。
• 这种写法和原查询逻辑完全一致，JOIN的方式相比IN子查询，在高并发场景下（每日80万次执行）更有可能被优化器处理为更高效的执行路径。

额外性能优化建议 #

考虑到查询执行频率极高，建议配合以下索引优化：

• 针对FB_POL_COUNTY || SUBSTR(CHAR(FB_POL_SERIAL),3,5)这个拼接条件，创建计算列索引，或者将该拼接结果持久化为表的物理列后建立索引，避免每次查询都执行字符串拼接运算。
• 对FB_POL_LOB字段创建前缀索引（CREATE INDEX IDX_FB_POL_LOB_PREFIX ON FB_POLICY(SUBSTR(FB_POL_LOB,1,4))），优化SUBSTR(FB_POL_LOB,1,4)的过滤效率。
• 结合主键和查询过滤字段，创建覆盖索引：CREATE INDEX IDX_FB_POLICY_QUERY_COVER ON FB_POLICY(FB_POL_COUNTY, FB_POL_SERIAL, FB_POL_STATUS_CD, FB_POL_POLICY_NO, FB_POL_GROUP_NO, FB_POL_ACTIVITY_TS)，让查询可以直接从索引获取所需数据，避免回表扫描。

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