我有一个大约有10亿行的表，它按date分区，并在someNr上有一个（本地）索引。此查询：
select max("date") from T where someNr = 1;如预期的那样在毫秒内执行。但是，如果没有带有someNr = 1的条目，则查询需要大约一分钟。然后我发现：
select "date" from T where someNr = 1 order by "date" desc fetch first 1 rows only;在毫秒内执行，无论是否存在带有someNr = 1的条目。max("date")的生产数据库中的实际计划：
order by的生产数据库中的实际计划：
找了一个多小时，也没找到满意的解释。要么他们没有正确的索引，使用rownum < 2，要么答案没有真正回答问题。
以下最小重现器与真实的场景类似，并显示了解释计划中的差异：
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'));输出：
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
+-------------------------------------------------+
|PLAN_TABLE_OUTPUT |
+-------------------------------------------------+
|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 ||
+-------------------------------------------------+
+-------------------------------------------+
|PLAN_TABLE_OUTPUT |
+-------------------------------------------+
|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的max()不够智能，不能按照日期降序排序，并以order by + fetch first 1 rows only相同的方式使用第一个值呢？我知道max()可能会返回null，而第二个选项不会，但这真的不应该有什么关系。
1条答案
按热度按时间oknwwptz1#
你说得对，Oracle的MIN/MAX索引算法不是很聪明。它似乎只能遍历索引一次，以获得最大或最小的值。Oracle通常不会将查询转换为使用MIN/MAX，因此您需要重写查询以利用该算法。
我不能真正回答 * 为什么 * 算法不聪明。我猜这是一个恶性循环，Oracle最初并没有投入太多精力来使MIN/MAX变得智能，因此它不经常使用，因此不值得投入更多精力来改进。MIN/MAX选项甚至没有在文档中的其他全面的PLAN_TABLE参考中列出。
作为MIN/MAX的一个示例，查询只能使用MIN或MAX，而不能同时使用两者。像
SELECT MIN(A),MAX(B) FROM SOME_TABLE这样的查询需要重写为SELECT (SELECT MIN(A) FROM SOME_TABLE), (SELECT MAX(B) FROM SOME_TALBE) FROM DUAL以利用MIN/MAX。有关此限制的更多详细信息，请参阅我的答案here。但是理解算法的局限性可以帮助我们设计一个更好的查询。我们可以使用一次MIN/MAX来快速获得最大值，然后将该值插入查询。下面的计划生成一个“INDEX FULL SCAN（MIN/MAX）”和一个“INDEX RANGE SCAN”，但是使用这些操作阅读索引两次可能比执行常规“INDEX FULL SCAN”的查询更快。