Oracle 18c中max()为何远不及order by+fetch first 1 rows only高效？

问题现象 #

我有一张约十亿行的表，按date字段分区，someNr字段有本地索引：

• 执行查询1：

  select max("date") from T where someNr = 1;
  

  当存在someNr = 1的记录时，查询可在毫秒内完成；但无匹配记录时，查询耗时约一分钟。
• 执行查询2：

  select "date" from T where someNr = 1 order by "date" desc fetch first 1 rows only;
  

  无论是否存在匹配记录，均能在毫秒内完成。

生产环境中两个查询的执行计划差异：

• MAX()查询：走PARTITION RANGE ALL + INDEX FULL SCAN (MIN/MAX) + SORT AGGREGATE
• ORDER BY查询：走PARTITION RANGE ALL + INDEX FULL SCAN + WINDOW NOSORT STOPKEY

最小复现示例 #

以下是复现该问题的SQL代码：

SELECT BANNER FROM v$version;

create table T ("DATE" date not null, SOME_NR int not null);
alter table T
  modify PARTITION BY RANGE ("DATE")
    INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
    (partition "old" values less than (to_date('01-JAN-2023', 'DD-MON-YYYY') ));
create index I_T_DATE ON T ("DATE", SOME_NR);

explain plan for
select max("DATE") from T where SOME_NR = 1;

select plan_table_output
from table (dbms_xplan.display('plan_table', null, 'basic'));

explain plan for
select "DATE" from T where SOME_NR = 1 order by "DATE" fetch first 1 rows only;

select plan_table_output
from table (dbms_xplan.display('plan_table', null, 'basic'));

执行计划输出 #

MAX()查询的执行计划 #

Plan hash value: 1357496781

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  SORT AGGREGATE              |          |
|   2 |   FIRST ROW                  |          |
|   3 |    PARTITION RANGE ALL       |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| I_T_DATE |
-------------------------------------------------

ORDER BY+FETCH查询的执行计划 #

Plan hash value: 3984456263

-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | SELECT STATEMENT       |          |
|   1 |  VIEW                  |          |
|   2 |   WINDOW NOSORT STOPKEY|          |
|   3 |    PARTITION RANGE ALL |          |
|   4 |     INDEX FULL SCAN    | I_T_DATE |
-------------------------------------------

核心原因分析 #

两个查询的性能差异源于Oracle对两种查询模式的优化逻辑不同：

1. MAX()查询的执行逻辑
   当执行MAX("DATE") where SOME_NR=1时，Oracle采用INDEX FULL SCAN (MIN/MAX)访问路径：

   • 该优化的设计是快速定位符合条件的极值，但必须扫描整个索引以确认无匹配记录时才能返回NULL。
   • 由于索引是(DATE, SOME_NR)的顺序，Oracle需要遍历所有分区的索引叶子块，逐一检查是否存在SOME_NR=1的记录。无匹配时，必须扫描完十亿行对应的所有索引块，因此耗时极长；有匹配时，找到最大DATE对应的符合条件条目后即可终止扫描，所以速度快。

2. ORDER BY + FETCH查询的执行逻辑
   当执行ORDER BY "DATE" DESC FETCH FIRST 1 ROWS ONLY时，Oracle启用WINDOW NOSORT STOPKEY优化：

   • 利用索引(DATE, SOME_NR)的升序特性，Oracle可以反向扫描索引（从最大的DATE开始），逐个检查条目是否满足SOME_NR=1。
   • 一旦找到第一个符合条件的记录，立即返回并终止扫描；即使无匹配记录，反向扫描结合STOPKEY机制也能更高效地遍历索引，无需强制扫描所有块即可确认无结果。
   • 此外，该查询无需执行聚合操作，避免了SORT AGGREGATE的额外开销。

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