in

select max(last_update_date),'2025-02' as period
from transaction_v
where domestic_invest_flag = 'Y'
  and status_code in ('09', '07', '99')
  and gl_date>= to_date('2025-02-01','yyyy-MM-dd') and gl_date  <= last_day(to_date('2025-02','yyyy-MM'))
  and org_id in (
      select hou.organization_id
      from access_sets_v gas
      where gas.name = 'XXX_ALL.name = 'CUX_CE_RECEIPT_ALL'
        and gas.organization_id not in(5581, 5582, 6706, 6062, 6082, 6122, 6245, 8856, 11676, 668)
  )

not in

select max(last_update_date),'2025-02' as period
from transaction_v
where domestic_invest_flag = 'Y'
  and status_code in ('09', '07', '99')
  and gl_date>= to_date('2025-02-01','yyyy-MM-dd') and gl_date  <= last_day(to_date('2025-02','yyyy-MM'))
  and org_id not in(5581, 5582, 6706, 6062, 6082, 6122, 6245, 8856, 11676, 668)

in 和 not in 对比

• in 查询需要 4 分钟
• not in 只需要 17 秒

为什么有 in 版本？

因为 in 版本的话会走对应字段的索引， not in 不一定走。

本案例中 not in 比 in 快的原因分析

• transaction_v 表数据量比较大，有几千万条
• in 中以下的子查询数据量也比较大，有 1041 条，导致组合查询起来较慢，进行了全表扫描

select hou.organization_id
from access_sets_v gas
where gas.name = 'XXX_ALL.name = 'CUX_CE_RECEIPT_ALL'
  and gas.organization_id not in(5581, 5582, 6706, 6062, 6082, 6122, 6245, 8856, 11676, 668)

• not in 数据量较小，避免了全表扫描
• 以上数据仅仅基于本案例，实际性能还需要考虑对应表数据，索引等情况。