为什么 select max(field) from table 比 select min(field) from table 工作得更快，Oracle 9i
我遇到了在 Oracle 9i 中运行 select max 语句的机会，它运行得非常快。
select max(id) from audit_log;
select min(id) from audit_log;
然而，当运行 select min 时，查询似乎挂起并且永远不会返回。该表包含审计日志和大约数亿条记录。
i ran into the chance of running a select max statement in Oracle 9i and it ran very fast.
select max(id) from audit_log;
select min(id) from audit_log;
However when running a select min, the query seems to hang and never return. This table has audit logs and some several hundred million records.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的最大值可能在内存中。如果它增加得很快，它很可能一直在内存中。如果没有人访问最小值，则必须检索该最小值。
您可能遇到了锁定问题。尝试更改隔离级别，看看是否会产生影响。
可能是您的索引已损坏。尝试重建它。
It could be that your max value is in memory. If it is incrementing quickly it is likely in memory all the time. If no one is accessing the min value it would have to be retrieved.
It could be that you are running into locking issues. Try changing the isolation level to see if that makes a difference.
It could be that your index is corrupt. Try rebuilding it.
一个可能的原因是列 ID确实有索引，但可以为 null。在这种情况下，ID 为 null 的行将不会出现在索引中。因此 min() 不能在可为空的列上使用索引。
...所以您可能只需要
alter tableaudit_logmodify id not null;或者您可以创建一个新的复合索引，并在ID后面添加一个非空列。这应该也有效，因为这样每一行都会有一个条目。
One possible reason would be that column ID does have an index, but can be null. In that case rows with ID null would not be in the index. Hence min() cannot use the index on a nullable column.
...so chances are you just have to
alter table audit_log modify id not null;Or you might create a new composite index with a non-null column behind ID. That should work as well, because then every row would have an entry.