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