Oracle已建索引但查询执行过慢，求SQL优化建议

方案1：使用NOT EXISTS重构子查询 #

SELECT UPPER(JCB.R4GSTATENAME) AS STATENAME,
       TM.JC_SAP_ID,
       COUNT(DISTINCT TM.FSAID) AS ACTUAL_VALUE,
       'FSA CWIP' AS CAT,
       'MONTHLY' AS DURATION,
       TM.constructordate
FROM FTTX_TASKMASTER TM
INNER JOIN (
    -- 提前对关联表去重，减少JOIN数据量
    SELECT SAP_ID, MAX(UPPER(R4GSTATENAME)) AS R4GSTATENAME
    FROM R4G_BU.JIOCENTERBOUNDARY
    GROUP BY SAP_ID
) JCB ON TM.JC_SAP_ID = JCB.SAP_ID
WHERE TM.constructordate >= TO_DATE('01-01-24', 'DD-MM-YY')
  AND TM.constructordate < TO_DATE('01-02-24', 'DD-MM-YY')
  AND NOT EXISTS (
      SELECT 1
      FROM FTTX_TASKMASTER TM_OLD
      WHERE TM_OLD.FSAID = TM.FSAID
        AND TM_OLD.constructordate <= TO_DATE('01-12-23', 'DD-MM-YY')
  )
  AND TM.STATUS IN ('COMPLETED', 'ACCEPTED', 'REJECTED')
  AND TM.JC_SAP_ID IS NOT NULL
GROUP BY TM.JC_SAP_ID, TM.constructordate, JCB.R4GSTATENAME;

方案2：使用LEFT JOIN替代NOT IN #

SELECT UPPER(JCB.R4GSTATENAME) AS STATENAME,
       TM.JC_SAP_ID,
       COUNT(DISTINCT TM.FSAID) AS ACTUAL_VALUE,
       'FSA CWIP' AS CAT,
       'MONTHLY' AS DURATION,
       TM.constructordate
FROM FTTX_TASKMASTER TM
INNER JOIN (
    SELECT SAP_ID, MAX(UPPER(R4GSTATENAME)) AS R4GSTATENAME
    FROM R4G_BU.JIOCENTERBOUNDARY
    GROUP BY SAP_ID
) JCB ON TM.JC_SAP_ID = JCB.SAP_ID
LEFT JOIN FTTX_TASKMASTER TM_OLD 
    ON TM.FSAID = TM_OLD.FSAID
    AND TM_OLD.constructordate <= TO_DATE('01-12-23', 'DD-MM-YY')
WHERE TM.constructordate BETWEEN TO_DATE('01-01-24', 'DD-MM-YY') AND TO_DATE('31-01-24', 'DD-MM-YY')
  AND TM.STATUS IN ('COMPLETED', 'ACCEPTED', 'REJECTED')
  AND TM.JC_SAP_ID IS NOT NULL
  AND TM_OLD.FSAID IS NULL
GROUP BY TM.JC_SAP_ID, TM.constructordate, JCB.R4GSTATENAME;

1. 替换NOT IN为NOT EXISTS/LEFT JOIN：
   • NOT IN存在两个核心问题：子查询返回NULL时会导致整个条件失效；Oracle优化器对其索引利用率远低于NOT EXISTS。NOT EXISTS一旦找到匹配记录就停止扫描，大幅提升子查询效率。
2. 重构关联子查询：
   • 将SELECT子句中逐行执行的子查询改为JOIN操作，通过提前对JIOCENTERBOUNDARY分组去重，减少JOIN阶段的数据量，避免重复计算。
3. 简化日期范围逻辑：
   • 用TO_DATE('01-02-24', 'DD-MM-YY')替代to_date('31-01-24','DD-MM-YY')+1，逻辑更清晰，避免日期计算可能引发的隐式转换。

针对现有索引未生效的问题，调整索引策略如下：

• 主查询覆盖索引：
  在FTTX_TASKMASTER上创建联合索引：

  CREATE INDEX idx_tm_construct_status_jc_fsa ON FTTX_TASKMASTER (constructordate, STATUS, JC_SAP_ID, FSAID);
  

  该索引包含WHERE过滤条件、GROUP BY字段和SELECT所需字段，实现索引覆盖扫描，无需回表查询原始数据。
• 子查询专用索引：
  为NOT EXISTS子查询创建索引：

  CREATE INDEX idx_tm_fsa_construct ON FTTX_TASKMASTER (FSAID, constructordate);
  

  快速匹配历史FSAID记录，避免全表扫描。
• 关联表索引：
  确保R4G_BU.JIOCENTERBOUNDARY的SAP_ID字段存在索引，加速JOIN匹配：

  CREATE INDEX idx_jcb_sap_id ON R4G_BU.JIOCENTERBOUNDARY (SAP_ID);
  

• 更新表统计信息：
  执行以下语句更新FTTX_TASKMASTER的统计信息，确保Oracle优化器生成最优执行计划：

  DBMS_STATS.GATHER_TABLE_STATS('你的Schema名', 'FTTX_TASKMASTER');
  

• 检查DISTINCT必要性：
  如果业务上JC_SAP_ID + constructordate分组内的FSAID唯一，可去掉COUNT(DISTINCT FSAID)中的DISTINCT，减少计算开销。
• 避免隐式类型转换：
  确认constructordate为DATE类型，查询中始终用TO_DATE显式转换字符串，防止隐式转换导致索引失效。

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