Oracle中OLAP函数与自连接获取最大值记录的性能对比咨询

日常业务中，经常需要从单表获取每个键对应某字段最大值/最新日期的记录，通常有两种实现方式：

• 子查询筛选键与最大值后自连原表
• 使用OLAP函数（如RANK()或ROW_NUMBER()）

当前我正在调优一个处理4000万条记录的Oracle查询，因执行计划（EXPLAIN）未必反映真实性能，不敢完全依赖。想了解这两种方案在Oracle环境下哪种更高效，个人认为OLAP版本可读性更强，寻求相关指导或经验分享。

示例代码 #

自连接版本 #

-- Self-join version:
with ords as (
        select 'AAA1' as ord_no, to_date('2023-01-01','yyyy-mm-dd') as ord_date, 'A' field1, 'B' field2 from dual union all
        select 'AAA1' as ord_no, to_date('2023-02-01','yyyy-mm-dd') as ord_date, 'C' field1, 'D' field2 from dual union all
        select 'AAA1' as ord_no, to_date('2023-03-01','yyyy-mm-dd') as ord_date, 'E' field1, 'F' field2 from dual union all
        select 'AAA1' as ord_no, to_date('2023-03-01','yyyy-mm-dd') as ord_date, 'E1' field1, 'F1' field2 from dual union all
        select 'BBB1' as ord_no, to_date('2023-01-01','yyyy-mm-dd') as ord_date, 'G' field1, 'H' field2 from dual union all
        select 'BBB1' as ord_no, to_date('2023-02-01','yyyy-mm-dd') as ord_date, 'I' field1, 'J' field2 from dual union all
        select 'BBB1' as ord_no, to_date('2023-03-01','yyyy-mm-dd') as ord_date, 'K' field1, 'L' field2 from dual
    ),
    max_ord as (
        select ord_no, max(ord_date) max_ord_date from ords group by ord_no
    )
    select mo.ord_no, mo.max_ord_date, o.field1, o.field2
    from max_ord mo join ords o on mo.ord_no = o.ord_no and mo.max_ord_date = o.ord_Date order by mo.ord_no;

OLAP函数版本 #

-- OLAP version
-- same data setup as above, omitted for brevity
select ord_no, ord_date, field1, field2 from 
    (select ord_no, ord_date, field1, field2, rank() over (partition by ord_no order by ord_date desc) maxdt
        from ords
    )
where maxdt = 1 order by ord_no;

预期结果 #

"ORD_NO"  "ORD_DATE"  "FIELD1"  "FIELD2"
"AAA1"    3/1/2023    "E"       "F"
"AAA1"    3/1/2023    "E1"      "F1"
"BBB1"    3/1/2023    "K"       "L"

内容的提问来源于stack exchange，提问作者JOATMON