仅做SQL修改来优化SQL

群里一朋友发来一段SQL，查询时间1分钟嫌太慢，让帮忙看一下。我优化SQL一般都是先看看是不是可以通过修改SQL来达到优化的目的。行不通的话，再去看执行计划，进一步做优化。（个人习惯，不一定是好习惯，嘿嘿）

原SQL：

select adjchar.adhoc_char_val l, ft.freeze_dttm
  from ci_adj adj, ci_adj_char adjchar, ci_sa sa, ci_ft ft
 where adj.adj_id = adjchar.adj_id
   and adj.adj_status_flg = '50'
   and adjchar.char_type_cd = 'CM_ICVOL'
   AND adj.sa_id = sa.sa_id
   and sa.sa_id = '3356800026'
   and adj.adj_id = ft.sibling_id
   AND NOT exists
 (select FT1.FT_ID
          from ci_adj adj, ci_adj_char adjchar, ci_sa sa, ci_ft ft1
         where adj.adj_id = adjchar.adj_id
           and adj.adj_status_flg = '50'
           and adjchar.char_type_cd = 'CM_ICVOL'
           AND adj.sa_id = sa.sa_id
           and sa.sa_id = '3356800026'
           and adj.adj_id = ft1.sibling_id
           AND (FT.SA_ID, FT.FREEZE_DTTM) IN
               (SELECT ft.SA_ID SA_id, MAX(ft.freeze_dttm)
                  from ci_adj adj, ci_adj_char adjchar, ci_sa sa, ci_ft ft
                 where adj.adj_id = adjchar.adj_id
                   and adj.adj_status_flg = '50'
                   and adjchar.char_type_cd = 'CM_ICVOL'
                   AND adj.sa_id = sa.sa_id
                   and sa.sa_id = '3356800026'
                   and adj.adj_id = ft.sibling_id
                 GROUP BY FT.SA_ID)
           and ft.ft_id = ft1.ft_id
           AND adjchar.adhoc_char_val < 0)

看了一遍后，很快就能看出来问题出现 NOT EXISTS 这个部分。ci_adj adj, ci_adj_char adjchar, ci_sa sa, ci_ft ft 四张表，作为一个整体放在NOT EXISTS里面做条件使用，且NOT EXISTS里面再次使用这四张表做IN的条件使用。基本上就明确了修改思路。

with tab as
 (select adjchar.adhoc_char_val l,
         ft.freeze_dttm,
         ft.ft_id,
         ft.sa_id,
         max(freeze_dttm) over(partition by ft.sa_id) max_freeze_dttm
    from ci_adj adj, ci_adj_char adjchar, ci_sa sa, ci_ft ft
   where adj.adj_id = adjchar.adj_id
     and adj.adj_status_flg = '50'
     and adjchar.char_type_cd = 'CM_ICVOL'
     AND adj.sa_id = sa.sa_id
     and sa.sa_id = '3356800026'
     and adj.adj_id = ft.sibling_id)
select t.l, t.freeze_dttm
  from tab t
 where not exists (select 1
          from tab t2
         where t2.freeze_dttm = t2.max_freeze_dttm
           and t2.adhoc_char_val < 0
           and t.ft_id = t2.ft_id)

使用WITH 语句把需要的关联结果做临时视图使用，WITH里面使用分析函数获取最大值，也就是 NOT EXISTS里面的IN的条件。

通过改写，不仅查询速度快了，而且整个SQL的逻辑也更加清晰了。