记一次SQL写法调优 with as order by多列

老业务员开发离职，留下一个SQL ，运行70多秒。
表为7-8亿行。

主键是3列的复合主键：jrn_no ,jrn_seq ,ac_dt,
组合索引： sep_cd，ac_dt

SELECT *
  from CSDACM.T_ACM_CDDT
 where sep_cd = '8080030000119045001'
   and ac_dt <= '20201223'
   and ac_dt >= '20201213'
   and ord_seq = (select max(ord_seq)
                    from CSDACM.T_ACM_CDDT
                   where sep_cd = '8080030000119045001'
                     and ac_dt <= '20201223'
                     and ac_dt >= '20201213')
   and jrn_no = (select max(jrn_no)
                   from CSDACM.T_ACM_CDDT
                  where sep_cd = '8080030000119045001'
                    and ord_seq != '0'
                    and ac_dt <= '20201223'
                    and ac_dt >= '20201213')
   and jrn_seq = (select max(jrn_seq)
                    from CSDACM.T_ACM_CDDT
                   where sep_cd = '8080030000119045001'
                     and ac_dt <= '20201223'
                     and ac_dt >= '20201213'
                     and ord_seq = (select max(ord_seq)
                                      from CSDACM.T_ACM_CDDT
                                     where sep_cd = '8080030000119045001'
                                       and ac_dt <= '20201223'
                                       and ac_dt >= '20201213')
                     and jrn_no = (select max(jrn_no)
                                     from CSDACM.T_ACM_CDDT
                                    where sep_cd = '8080030000119045001'
                                      and ord_seq != '0'
                                      and ac_dt <= '20201223'
                                      and ac_dt >= '20201213'))；

分析发现就是一个查询结果集来回循环，想到 with as。但开始时候没想，直接把结果集放到查询子集里试试。

select c.* from (SELECT a.* from CSDACM.T_ACM_CDDT a,
  (select max(ord_seq) os,max(jrn_no) jn 
  from CSDACM.T_ACM_CDDT where sep_cd = '8080030000119045001' and ac_dt <= '20201223' and ac_dt >= '20201213') b 
 where a.sep_cd = '8080030000119045001'
   and a.ac_dt <= '20201223'
   and a.ac_dt >= '20201213'
   and a.ord_seq=b.os
   and a.jrn_no=b.jn
   order by a.jrn_seq desc) c where rownum=1 

查询减少到30-40秒，看着还是有点乱，但是长度降低下来了。

继续用with as 试试：

with a as (select * from CSDACM.T_ACM_CDDT  where sep_cd = '8080030000119045001' and ac_dt <= '20201223' and ac_dt >= '20201213')
select c.* from (select * from a where (ord_seq,jrn_no) in (select max(ord_seq),max(jrn_no) from a)  order by jrn_seq desc) c where rownum=1;

结果9-12秒内出来结果。

记录下： order by多列取最大值所在的整行数据;

select * from A where （b，c） in (select max(a),max(c) from A)

with max_bc as (select * from A where (b，c) in (select max(a),max(c) from A))
select * from   max_bc where e in (select max(e) from  max_bc)