可替代MINUS查询的SQL实现方案 解决跨表比对查询性能过慢问题

原查询的核心目标是排查「在EDWHRSTG.PS_JOB_FULL_S表存在、但在SUODS.PS_JOB_S表不存在的、指定员工的五个字段完全匹配的差异记录」，原MINUS逻辑因全量排序、去重的固有执行机制存在严重性能问题，可通过以下两种等价方案实现同等效果，且性能远优于原逻辑。

优先推荐：NOT EXISTS半连接实现 #

该方案的执行逻辑为逐行从主表取记录，到关联表逐字段匹配，一旦匹配到符合条件的记录就立即短路返回，不需要对两个结果集做全量排序、全量去重，可最大化利用索引减少扫描开销，且执行结果和原MINUS完全一致。

SELECT t1.EMPLID, t1.EMPL_RCD, t1.EFFDT, t1.HR_STATUS, t1.EMPL_STATUS
FROM EDWHRSTG.PS_JOB_FULL_S t1
WHERE 
  t1.EMPLID = '09762931'
  AND NOT EXISTS (
    SELECT 1 
    FROM SUODS.PS_JOB_S t2
    WHERE 
      -- 关联条件需和原MINUS的逐字段比对规则完全对齐，不可漏字段
      t2.EMPLID = t1.EMPLID
      AND t2.EMPL_RCD = t1.EMPL_RCD
      AND t2.EFFDT = t1.EFFDT
      AND t2.HR_STATUS = t1.HR_STATUS
      AND t2.EMPL_STATUS = t1.EMPL_STATUS
  )

备选方案：左连接后置过滤实现 #

当数据库对NOT EXISTS的执行计划优化不佳时，可使用左连接匹配后过滤空值的逻辑，执行结果同样和原MINUS等价。

SELECT t1.EMPLID, t1.EMPL_RCD, t1.EFFDT, t1.HR_STATUS, t1.EMPL_STATUS
FROM EDWHRSTG.PS_JOB_FULL_S t1
LEFT JOIN SUODS.PS_JOB_S t2
  ON t2.EMPLID = t1.EMPLID
  -- 逐字段关联条件和原MINUS比对规则完全对齐
  AND t2.EMPL_RCD = t1.EMPL_RCD
  AND t2.EFFDT = t1.EFFDT
  AND t2.HR_STATUS = t1.HR_STATUS
  AND t2.EMPL_STATUS = t1.EMPL_STATUS
WHERE 
  t1.EMPLID = '09762931'
  AND t2.EMPLID IS NULL

原MINUS逻辑性能瓶颈说明

• 数据库执行MINUS操作时，会分别对两个子查询的结果集做全量去重、全字段排序，再逐行比对差异，哪怕已经通过EMPLID过滤缩小了结果范围，排序、去重的固有开销仍然很高
• 当关联表没有匹配的复合索引时，排序耗时会随数据量线性上涨
• 不推荐使用NOT IN实现该逻辑：当关联表的匹配字段存在NULL值时，NOT IN的返回结果会和原MINUS逻辑出现偏差，且执行计划同样存在全量排序的额外开销

配套优化建议 #

在SUODS.PS_JOB_S表上建立覆盖索引(EMPLID, EMPL_RCD, EFFDT, HR_STATUS, EMPL_STATUS)，关联时直接从索引中取数无需回表，可进一步将查询耗时压缩到毫秒级。

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