DB2子查询最佳实践：9个关联子查询慢查询优化咨询

你的原始查询使用了9个关联子查询，每查询一条TLORDER的记录就要对ODRSTAT表做9次独立扫描，数据量大时性能损耗会非常明显，可通过以下方案优化：

优化后的SQL语句 #

SELECT 
    t.BILL_NUMBER,
    MIN(CASE WHEN os.STATUS_CODE = 'ASSGN' THEN os.Changed END) AS "FIRST ASSGN",
    MAX(CASE WHEN os.STATUS_CODE = 'ASSGN' THEN os.Changed END) AS "LAST ASSGN",
    MAX(CASE WHEN os.STATUS_CODE = 'D-CPICKED' THEN os.Changed END) AS "LAST DC PICKED",
    MIN(CASE WHEN os.STATUS_CODE = 'CONTACTED' THEN os.Changed END) AS "FIRST CONTACTED STATUS",
    MIN(CASE WHEN os.STATUS_CODE = 'UNLOADED' THEN os.Changed END) AS "FIRST UNLOADED STATUS",
    MIN(CASE WHEN os.STATUS_CODE = 'CHECKIN' THEN os.Changed END) AS "FIRST CHECKIN STATUS",
    MIN(CASE WHEN os.STATUS_CODE = 'LHDISP' THEN os.Changed END) AS "FIRST LHDISP STATUS",
    MAX(CASE WHEN os.STATUS_CODE = 'LHDISP' THEN os.Changed END) AS "LAST LHDISP STATUS",
    MAX(CASE WHEN os.STATUS_CODE = 'D-DISP' THEN os.Changed END) AS "LAST DDISP STATUS"
FROM TMWIN.TLORDER t
LEFT JOIN TMWIN.ODRSTAT os 
    ON CHAR(os.ORDER_ID) = t.BILL_NUMBER
    AND os.STATUS_CODE IN ('ASSGN','D-CPICKED','CONTACTED','UNLOADED','CHECKIN','LHDISP','D-DISP')
WHERE t.pick_up_by >= '2021-08-01' 
  AND t.pick_up_by <= '2021-08-03'
GROUP BY t.BILL_NUMBER

额外性能优化建议 #

• 避免函数包裹关联字段：如果ODRSTAT.ORDER_ID的数据类型和TLORDER.BILL_NUMBER一致，去掉CHAR(os.ORDER_ID)的转换，函数会导致索引失效。如果类型确实不一致，建议统一字段类型，或者提前基于转换后的结果创建函数索引。
• 新增联合索引：在ODRSTAT表上创建联合索引(STATUS_CODE, ORDER_ID, Changed)，覆盖查询用到的所有字段，查询时可直接走索引无需回表，性能提升会非常明显。
• 日期格式标准化：建议使用DB2原生的日期格式'YYYY-MM-DD'替代'8/1/2021'这类格式，避免隐式转换带来的性能损耗和日期解析错误。

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