为什么选择1比选择计数（*）快？ [英] Why is Select 1 faster than Select count(*)?

问题描述

在Oracle中，当查询行存在时，为什么选择1快于选择计数（*）？

In Oracle, when querying for row existence, why is Select 1 fast than Select count(*)?

推荐答案

Oracle在PL / SQL中不支持IF EXISTS，CodeByMidnight的建议使用EXISTS通常使用类似

Since Oracle doesn't support IF EXISTS in PL/SQL, CodeByMidnight's suggestion to use EXISTS would normally be done with something like

SELECT 1 
  INTO l_local_variable 
  FROM dual 
 WHERE EXISTS( 
    SELECT 1 
      FROM some_table 
     WHERE some_column = some_condition ); 

Oracle知道一旦找到一行，它就可以停止处理WHERE EXISTS子句，不必潜在地计数与该标准匹配的大量行。当然，如果您要检查具有特定键的行是否存在，而不是检查包含非索引列的条件或检查可能导致返回大量行的条件，那么这不是一个问题。

Oracle knows that it can stop processing the WHERE EXISTS clause as soon as one row is found, so it doesn't have to potentially count a large number of rows that match the criteria. This is less of a concern, of course, if you are checking to see whether a row with a particular key exists than if you are checking a condition involving unindexed columns or checking a condition that might result in a large number of rows being returned.

（注意：我希望我可以发表评论CodeByMidnight的帖子，但评论不能包括格式化代码）。

(Note: I wish I could post this as a comment on CodeByMidnight's post, but comments can't include formatted code).

更新：鉴于原始海报在他们的评论中做出的澄清，简短的，明确的答案是 SELECT 1 或 SELECT COUNT（1）不比 SELECT COUNT（*）快。与您正在查看的任何编码指南相反， COUNT（*）是计算所有行的首选方法。有一个古老的神话，一个 COUNT（1）更快。至少，在过去十年中发布的任何版本的Oracle中都不是这样，这是不可能的，它是真实的。然而，这是一个广泛持有的信念。今天，执行 COUNT（1）而不是 COUNT（*）的代码通常让我怀疑作者很容易相信各种Oracle神话，这就是为什么我建议使用 COUNT（*）。

UPDATE: Given the clarification the original poster made in their comment, the short, definitive answer is that a SELECT 1 or SELECT COUNT(1) is no faster than a SELECT COUNT(*). Contrary to whatever coding guidelines you are looking at, COUNT(*) is the preferred way of counting all the rows. There was an old myth that a COUNT(1) was faster. At a minimum, that hasn't been true in any version of Oracle released in the past decade and it is unlikely that it was ever true. It was a widely held belief, however. Today, code that does a COUNT(1) rather than a COUNT(*) generally makes me suspect that the author is prone to believe various Oracle myths which is why I would suggest using COUNT(*).

这篇关于为什么选择1比选择计数（*）快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！