Oracle 最值的优化 -- 利用分析函数改写

        以下案例及写法来自 triger liu《专题培训-SQL写法与改写》，有部分个人测试及删改，原文请参考原课程。

一、 最值在表关联中

1. 低效且有重复值的写法

找出各部门工资最高的员工信息

select a.EMPNO, a.ENAME, a.JOB, a.MGR, a.HIREDATE, a.SAL, a.COMM, a.DEPTNO
  from emp a,
       (select deptno, max(sal) as max_sal from emp group by deptno) b
 where a.deptno = b.deptno
   and a.sal = b.max_sal
 order by DEPTNO

这个SQL有两个问题：

• 如果每个部门是最高工资的不止一个人，上面查询会有重复数据。如果每个部门最高工资只要取一个人，这个写法就是错的。

•  emp 表扫描了两次，效率低

2. 高效且有重复值的写法

       等价改写是使用max()分析函数，改写之后依然有重复值，但是emp表只扫描一次。如果确实是需要重复值的，就用这个方法改写。

select empno,ename,job,mgr,hiredate,sal,comm,deptno
 from (select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.comm,a.deptno,a.sal,
       max(sal) over(partition by deptno) as max_sal
     from emp a)
where sal = max_sal
order by deptno;

• 数据不去重

•  emp表仅扫描一次

分析函数除了最常用的row_number，其实还有很多种，在不同场景下可以发挥妙用。

3. 高效且去重的写法

需要使用row_number分析函数

select *
  from (select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.comm,a.sal,a.deptno,
               row_number() over(partition by deptno order by sal desc) as rn
          from emp a)
 where rn = 1
 order by deptno

• 数据去重

•  emp表只扫描一次

4. 实际案例

凌晨CPU炸裂，查看top cpu sql，发现就是类似的语句，单次执行超过4万秒

如果是想取各个product_code最大global_version的信息，这个写法会有重复的数据

可以用分析函数改写，最后这个order by不需要也可以不排

改写后1.5秒出结果，cpu消耗基本没有了

二、 最值在where条件中

还是找出各部门工资最高的员工信息，但我们这次换个写法

select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.comm,sal,a.deptno
from emp a
where sal = (select max(sal)
             from emp b
             where b.deptno =a.deptno)
order by deptno;

       注意蓝色部分虽然没有写group by，但它的实际含义就是按deptno分组取最大值，所以如果改成分析函数一定得记得加 partition by deptno

你会发现问题是一样的：

• 数据不去重

• emp表扫描两次

​

它的改写方法跟前面的一样的，这里不重复了

三、 取最值的最值

1. 测试表构造

create table TT_LOG(ID int,SYNC_DATE int,JOUR_NO int);
insert into TT_LOG values(333,1,4);
insert into TT_LOG values(333,2,3);
insert into TT_LOG values(333,2,2);
insert into TT_LOG values(666,2,5);
insert into TT_LOG values(666,2,3);
--插入些重复值
insert into TT_LOG values(666,3,4);
insert into TT_LOG values(666,3,4);
insert into TT_LOG values(666,3,2);
commit;

 

2. 原SQL

根据ID分组，取 SYNC_DATE 最大值，再取最大SYNC_DATE对应的最大 JOUR_NO。

SELECT B.ID, B.SYNC_DATE, B.JOUR_NO
FROM
(SELECT ID, MAX(SYNC_DATE) SYNC_DATE
FROM TT_LOG
GROUP BY ID
) A,
(SELECT ID, SYNC_DATE, MAX(JOUR_NO) JOUR_NO
FROM TT_LOG
GROUP BY ID, SYNC_DATE
) B
WHERE A.ID = B.ID
AND A.SYNC_DATE = B.SYNC_DATE;

上面的SQL虽然实现了功能，但扫描了两次 tt_log 表，效率较低。

 

 3. 改写

       事实上可以直接用分析函数实现该功能：根据ID分组，根据 SYNC_DATE和JOUR_NO 字段按分组排序，然后取最大值。

• 先看看取最大值前的数据是什么样的

SELECT ID,SYNC_DATE,JOUR_NO,
row_number() over(partition BY ID order by SYNC_DATE DESC,JOUR_NO DESC) rn
FROM TT_LOG;

       对每个id值，按照 SYNC_DATE和JOUR_NO 倒序排序，并列出编号，其中RN=1的数据就是我们需要的。

• 加上取最大值

SELECT B.ID,B.SYNC_DATE,B.JOUR_NO
FROM
(SELECT ID,SYNC_DATE,JOUR_NO,
row_number() over(partition BY ID order by SYNC_DATE DESC,JOUR_NO DESC) rn
FROM TT_LOG
) B
WHERE rn=1;

数据正确，并且表只扫描了一次，更高效。

 

四、 一个复杂些的例子

1. 原sql

select count(*)
from jbpm_testtest1 t
join jbpm_testtest2 pi on t.procinst_=Pi.id_
inner join ccform_testtest3 d on d.cf_id = pi.id_ 
where ( t.end_= (select max(end_)
        from jbpm_testtest1 s
        where s.procinst_=d.cf_id
           and d.cf_gdsta in ('K')
        )
    or (t.end_ is null and t.isopen =1 )
);

同样虽然求最值部分没有明着用group by，但因为它是在where条件中通过 procinst_ 字段关联，实际上就是对 procinst_ 字段分组求最值。

2. 比较直接的改法

直接把求max值部分提出作为临时表，然后改成关联（已经比原sql高效）

with V as
(
select procinst_,max(end_) as max_end_
from jbpm_testtest1
group by procinst_ -- 注意这里一定要有
)
select count(*)
from
( select t.procinst_,t.end_,t.isopen,d.cf_id,d.cf_gdsta,v.max_end_
  from jbpm_testtest1 t
  inner join jbpm_testtest2 pi
  on t.procinst_=Pi.id_
 inner join ccform_testtest3 d
  on d.cf_id = pi.id_
  left join v on
  (d.cf_id=v.procinst_ and d.cf_gdsta in ('K'))
) where (end_ is null and isopen=1) or (end_ = max_end_ );

3. 改为分析函数

不要只想着把 jbpm_testtest1部分拆出来改为分析函数后再跟其他表关联，这里他们是一个整体。

• 所以第一步，先把它拆成 关联部分+条件部分

select count(*) from ( select xxxx from

from jbpm_testtest1 t
join jbpm_testtest2 pion t.procinst_=Pi.id_
inner join ccform_testtest3 don d.cf_id = pi.id_

) as tmp

where ( t.end_= (select max(end_)
    from jbpm_testtest1 s
    where s.procinst_=d.cf_id
     and d.cf_gdsta in ('K')
    )

• 第二步，把紫色部分看做一个整体，那xxxx部分应该是什么？ —— 是where条件中的字段，所以把where条件中用到的字段挪上去

select count(*) from ( select t.procinst_,t.end_,t.isopen,d.cf_id,d.cf_gdsta,max_end

from jbpm_testtest1 t
join jbpm_testtest2 pion t.procinst_=Pi.id_
inner join ccform_testtest3 don d.cf_id = pi.id_

) as tmp

where ( t.end_= (select max(end_)
    from jbpm_testtest1 s
    where s.procinst_=d.cf_id
     and d.cf_gdsta in ('K')
    )
  or (t.end_ is null and t.isopen =1 )
);

• 第三步，max_end 是怎么来的，就是通过分析函数，所以把这部分加上分析函数

select count(*) from ( select t.procinst_,t.end_,t.isopen,d.cf_id,d.cf_gdsta,
                       max(t.end_) over(partition by t.procinst_) as max_end
from jbpm_testtest1 t
join jbpm_testtest2 pion t.procinst_=Pi.id_
inner join ccform_testtest3 don d.cf_id = pi.id_
) as tmp
where ( t.end_= (select max(end_)
    from jbpm_testtest1 s
    where s.procinst_=d.cf_id
     and d.cf_gdsta in ('K')
    )
  or (t.end_ is null and t.isopen =1 )
);

• 最后，既然已经有了max_end，where条件那个t.end=max(end_) 就可以替换掉了。当然，其他条件要保留

select count(*) from ( select t.procinst_,t.end_,t.isopen,d.cf_id,d.cf_gdsta,
                       max(t.end_) over(partition by t.procinst_) as max_end
from jbpm_testtest1 t
join jbpm_testtest2 pion t.procinst_=Pi.id_
inner join ccform_testtest3 don d.cf_id = pi.id_
) as tmp
where (end_= max_end and procinst_=d.cf_id and cf_gdsta in ('K'))
  or (end_ is null and isopen =1);

最后结果

select count(*)
from
( select t.procinst_,t.end_ , t.isopen,d.cf_id,d.cf_gdsta,
  max(end_) over (partition by t.procinst_) as max_end
from jbpm_testtest1 t
join jbpm_testtest2 pion t.procinst_=Pi.id_
inner join ccform_testtest3 don d.cf_id = pi.id_
) as tmp
where (end_=max_endand cf_id=procinst_ and cf_gdsta in ('K')) or (end_ is null and isopen=1) ;

参考

SQL优化经典----利用分析函数优化自连接_mengxiangfeiyang的专栏-优快云博客

利用分析函数优化自连接_落落的专栏 专注SQL调优 性能调优-优快云博客

用oracle分析函数优化自连接_kingtoon的专栏-优快云博客

28 分析函数优化自连接，减少表访问次数 --优化主题系列_leo0805的博客-优快云博客

第62篇-学习并分析“一次巧妙的SQL优化改写”