带有 ORDER BY 的 SELECT COUNT(*)
下面两个查询会以同样的方式执行吗？
SELECT COUNT(*) from person ORDER BY last_name;
和
SELECT COUNT(*) from person;
无论哪种方式，它们都应该显示相同的结果，所以我很好奇 < code>ORDER BY 只是被忽略。
我问的原因是因为我正在显示一个分页表，我将从数据库中一次获取 20 条记录，然后触发第二个查询来计算记录总数。我想知道是否应该使用第一个查询使用的相同条件，或者是否应该从条件中删除所有排序？
Will the following two queries be executed in the same way?
SELECT COUNT(*) from person ORDER BY last_name;
and
SELECT COUNT(*) from person;
Either way they should display the same results, so I was curious if the ORDER BY just gets ignored.
The reason I am asking is because I am displaying a paginated table where I will get 20 records at a time from the database and then firing a second query that counts the total number of records. I want to know if I should use the same criteria that the first query used, or if I should be removing all sorting from the criteria?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据执行计划，两个查询是不同的。例如，查询：
将给出：
如您所见，我们点击了 USER_PK，它是该表的主键。
如果我按非索引列排序：
我会得到：
意味着我们进行了全表扫描（节点成本高得多）
如果我按主键（已经是索引）排序，Oracle 足够聪明，可以使用索引进行这种排序：
这看起来与第一个执行计划非常相似。
所以，你的问题的答案绝对不是——它们不一样。然而，按 Oracle 已经在寻找的索引进行排序可能会产生相同的查询计划。
According to the execution plan, the two queries are different. For example, the query:
Will give me:
As you can see, we hit USER_PK which is the primary key of that table.
If I sort by a non-indexed column:
I'll get:
Meaning we did a full table scan (MUCH higher node cost)
If I sort by the primary key (which is already index,) Oracle is smart enough to use the index to do that sort:
Which looks very similar to the first execution plan.
So, the answer to your question is absolutely not - they are not the same. However, ordering by an index that Oracle is already seeking anyway will probably result in the same query plan.
当然不是。除非姓氏是主键并且您已经按此排序。
Of course not. Unless last name is the primary key and you are already ordered by that.
Oracle 查询优化器实际上确实对第一个版本执行了排序（我通过查看解释计划验证了这一点），但由于两个查询仅返回一行，因此性能差异将非常小。
编辑：
迈克的答案是正确的。性能差异可能很大。
The Oracle query optimizer actually does perform a sort (I verified this looking at the explain plan) for the first version, but since both queries only return one row, the performance difference will be very small.
EDIT:
Mike's answer is correct. The performance difference can possibly be significant.