SQL查询优化

最近在优化一个SQL查询过程中，发现使用NOT EXISTS的方式执行速度缓慢且难以获得结果。

原始脚本如下：

SELECT COUNT(1) FROM (
  SELECT /*+ full(t) */
    t.*
  FROM cpe_demands_t t
  LEFT JOIN cpe_items_i_t i ON t.demand_item = i.item_code
  WHERE t.demand_id IS NOT NULL
    AND t.batch_id = 51115
    AND t.max_bom_level = 3
    AND NOT EXISTS (
      SELECT /*+ parallel(kk, 10) */
        1
      FROM iscp_atp.cpe_pegging_t kk
      WHERE kk.batch_id = 51115
        AND kk.max_bom_level = 3
        AND kk.demand_id = t.demand_id
    )
    AND t.demand_date >= SYSDATE - 7 * 7
    AND t.demand_date < SYSDATE + 1 * 7
)

通过更改为LEFT JOIN的方式后，性能显著提升。在相同数据量下，NOT EXISTS方式耗时422秒，而LEFT JOIN仅耗时0.65秒，性能提升高达649倍！

改进后的脚本优化如下：

SELECT COUNT(1) FROM (
  SELECT * FROM (
    SELECT /*+ full(t) */
      t.*
    FROM cpe_demands_t t
    LEFT JOIN cpe_items_i_t i ON t.demand_item = i.item_code
    WHERE t.demand_id IS NOT NULL
      AND t.batch_id = 51115
      AND t.max_bom_level = 3
      AND t.demand_date >= SYSDATE - 7 * 7
      AND t.demand_date < SYSDATE + 1 * 7
  ) tt 
  LEFT JOIN cpe_pegging_t kk ON kk.demand_id = tt.demand_id
  WHERE kk.demand_id IS NULL
)

通过这次优化，我们发现将NOT EXISTS替换为LEFT JOIN能显著提升SQL查询性能。更多详情请参考原文链接：Oracle SQL 优化：NOT EXISTS改为LEFT JOIN提升性能。