DB2查询运行缓慢 索引顾问无推荐 恳请排查语句优化点

以下是你提供的查询中存在的明显性能问题，按影响优先级排序：

1. 致命逻辑问题：无关联条件的笛卡尔积 #

主查询FROM SUBSIDIARY,"ORDER" A没有写两个表的关联条件，直接触发笛卡尔积计算。如果SUBSIDIARY有N行、"ORDER"表有M行，会先生成N*M的中间结果集，哪怕两个表数据量不大，也会导致中间结果暴增几十上百倍，这是查询慢的核心原因之一。请补充两个表的关联条件，例如SUBSIDIARY.SUBDIV = A.ORDV#这类业务关联逻辑。

2. 冗余无效的CTE定义 #

• PREORDER CTE完全没有被后续逻辑引用，属于无效代码，直接删除即可。
• ORDERMILES CTE逻辑完全重复，你主查询已经直接关联了MMILES表且带了同样的过滤条件，这个CTE会额外多扫一次MMILES表，直接删除即可。
• STOPGROUP CTE中冗余关联了"ORDER"表，该关联没有用到任何"ORDER"表的字段或过滤条件，直接删掉这段关联，仅保留STOPOFF表的查询即可，减少一次"ORDER"表的扫描。
• TCALLORDER CTE中再次查询了"ORDER"表，和主查询的"ORDER"表扫描重复，可以将这段逻辑合并到主查询的关联中，避免重复扫描大表。

3. 低效的CTE实现逻辑 #

• FIRSTEQUIP相关的两段CTE，等于扫两次OPEQUIP表：第一次分组取最小RRN，第二次回表取设备号。可以改写为窗口函数一次扫描完成：

FIRSTEQUIPROW AS (
  SELECT OEORD#, OETRLR EQUIPMENTNUMBER
  FROM (
    SELECT OEORD#, OETRLR,
      ROW_NUMBER() OVER(PARTITION BY OEORD# ORDER BY RRN(A)) rn
    FROM OPEQUIP A
  ) T WHERE rn = 1
)

改写后仅需扫描一次OPEQUIP表，性能提升一倍。

4. 过滤条件未下推 #

主查询的核心过滤条件ORSTAT <> 'C'（排除已关闭订单）没有下推到所有引用"ORDER"表的逻辑中，导致所有CTE先扫描全量订单数据，最后才过滤掉大部分无效数据。你需要在所有用到"ORDER"表的地方都加上ORSTAT <> 'C'的过滤条件，先把数据量降下来再做后续计算。

5. 缺少必要索引（索引顾问无推荐的原因） #

索引顾问没有推荐通常是因为你的查询关联、过滤的列完全没有基础索引，先建好以下基础索引，大部分场景下性能会有量级提升：

• "ORDER"表：联合索引(ORSTAT, ORODR#)，覆盖过滤和关联字段
• OPEQUIP表：索引(OEORD#)，包含OETRLR字段（覆盖索引，无需回表）
• STOPOFF表：联合索引(SOARDT, SOORD)，包含SOSTP#, SOAPPR, SOAPTM字段
• MMILES表：联合索引(MMORD#, MMRECTYPE, MMDSP#)，包含MMTOTAL字段
• LMNEG表：索引(BNORD#)，用于分组统计
• 常用关联表的关联列必须建索引：CUSTMAST(CUCODE)、MCCSTLLP(C#CODE)、CITIES(CIST, CICTY)

6. 其他优化点 #

• 你提到后续用UNION拼接另一半查询，如果业务上不需要去重，把UNION改成UNION ALL，避免全量排序去重的开销，性能至少提升30%以上。
• SELECT中多次调用CVTDATETIM函数，且对日期字段做了CHAR()转换，会导致索引失效且耗CPU。如果业务允许，建议提前把日期+时间存储为标准TIMESTAMP格式，或者针对转换逻辑建函数索引。

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