我有一个大约有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”的查询更快。