仅做SQL修改来优化SQL

来源：互联网发布：131458查询淘宝账号编辑：程序博客网时间：2024/05/01 04:28

群里一朋友发来一段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的逻辑也更加清晰了。

0 0