Max()为什么在解释计划中通过创建订单? [英] Why does Max() create an order by in the explain plan?

问题描述

当我尝试做类似的事情

SELECT Max(ObjectId) FROM Objects;

我在解释计划中看到，这是通过执行排序来完成的.现在，排序(我想可能需要复杂度为O(nlogn)的排序)必须比仅扫描每一行并记住最大值(可以在O(n)中完成)要昂贵得多.

I see that in the explain-plan that this is performed by doing a sort. Now, sorting (which I guess would require something in the complexity of O(nlogn)) must be much costlier than just scanning each row and remembering the max value (which could be done in O(n)).

我在这里错过了什么吗? oracle是在执行排序吗，还是解释计划仅使用描述"sort"来描述对ObjectId列中所有值的简单扫描?如果oracle确实执行了真正的排序"，那么我是否缺少这样做的充分理由?

Am I missing something here? Is oracle really performing a sort or does the explain-plan just use the description "sort" for describing a simple scan of all values in the ObjectId column? If oracle really does perform a "real sort", is there a good reason for doing this that I am missing?

提前谢谢！

推荐答案

由于您尚未发布有关表Objects的详细信息，我们将不得不猜测.我的猜测是您在ObjectId上有一个索引.在这种情况下，您将在说明计划中看到INDEX FULL SCAN(MIN/MAX)步骤，这意味着将直接从索引中检索数据.这些键按索引排序，因此读取第一个或最后一个键将为您提供MIN/MAX.

since you have not posted details about your table Objects we will have to guess. My guess is that you have an index on ObjectId. In that case you will see a INDEX FULL SCAN (MIN/MAX) step in the explain plan, meaning that the data will be retrieved directly from the index. The keys are ordered in an index so reading the first or last key gives you the MIN/MAX.

这是O(log n)操作(因为它取决于索引的深度).

This is an O(log n) operation (since it depends upon the depth of the index).

如果您没有ObjectId上的索引，您将在说明计划中看到SORT AGGREGATE步骤.这并不意味着将对整个集合进行排序.实际上，数据将在读取时进行汇总.这很可能需要对每一行进行一次比较，从而使您获得总O(n)成本.

If you don't have an index on ObjectId you will see a SORT AGGREGATE step in the explain plan. This doesn't mean that the entire set will be sorted. In fact the data will be aggregated as it is read. This will likely involve a single comparison for each row, giving you a total O(n) cost.

在相关说明中，Oracle可能使用 O(n)算法排序数据.

Also on a related note, Oracle probably uses O(n) algorithms to sort data.

这篇关于Max()为什么在解释计划中通过创建订单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！