oracle 为什么 Select 1 比 Select count(*) 快？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1406099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is Select 1 faster than Select count(*)?
提问by Justin Cave
In Oracle, when querying for row existence, why is Select 1 fast than Select count(*)?
在Oracle 中，查询行是否存在时，为什么Select 1 比Select count(*) 快？
回答by MartW
It is better still to use EXISTS where the RDBMS supports it or an equivalent, as this will stop processing rows as soon as it finds a match.
最好还是在 RDBMS 支持的情况下使用 EXISTS 或等效项，因为这将在找到匹配项后立即停止处理行。
回答by Justin Cave
Since Oracle doesn't support IF EXISTS in PL/SQL, CodeByMidnight's suggestion to use EXISTS would normally be done with something like
由于 Oracle 不支持 PL/SQL 中的 IF EXISTS，CodeByMidnight 建议使用 EXISTS 通常会用类似的方法完成
SELECT 1
INTO l_local_variable
FROM dual
WHERE EXISTS(
SELECT 1
FROM some_table
WHERE some_column = some_condition );
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.
Oracle 知道它可以在找到一行后立即停止处理 WHERE EXISTS 子句，因此它不必潜在地计算与条件匹配的大量行。当然，与检查涉及未索引列的条件或检查可能导致返回大量行的条件相比，如果您要检查具有特定键的行是否存在，则这不是一个问题。
(Note: I wish I could post this as a comment on CodeByMidnight's post, but comments can't include formatted code).
（注意：我希望我可以将此作为对 CodeByMidnight 帖子的评论发布，但评论不能包含格式化代码）。
UPDATE: Given the clarification the original poster made in their comment, the short, definitive answer is that a SELECT 1or 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(*).
更新：鉴于原始海报在他们的评论中所做的澄清，简短而明确的答案是 a SELECT 1orSELECT COUNT(1)不比 a 快SELECT COUNT(*)。与您正在查看的任何编码指南相反，COUNT(*)是计算所有行的首选方法。有一个古老的神话，aCOUNT(1)更快。至少，在过去十年发布的任何 Oracle 版本中都不是这样，而且不太可能是真的。然而，这是一个广泛持有的信念。今天，执行 aCOUNT(1)而不是 a 的代码COUNT(*)通常让我怀疑作者倾向于相信各种 Oracle 神话，这就是为什么我建议使用COUNT(*).
回答by cjk
I'd be suprised if select count(*) wasn't properly optimised, there is no need to load in all the columns as there will be no column related processing.
如果 select count(*) 没有正确优化，我会感到惊讶，因为没有与列相关的处理，所以不需要加载所有列。
回答by Vinen
http://www.dbasupport.com/forums/archive/index.php/t-28741.html
http://www.dbasupport.com/forums/archive/index.php/t-28741.html
For Oracle at least.
至少对于 Oracle。
回答by Mario Mueller
Because a star takes all cols into the count, "1" is a native datatype.
因为星号将所有列都计算在内，所以“1”是本机数据类型。
In MySQL "SELECT COUNT(name_of_the_primary_key)" should be as fast as your SELECT 1. Its the index that counts. A count() on an index should be quite fast ;)
在 MySQL 中，“SELECT COUNT(name_of_the_primary_key)”应该和你的 SELECT 1 一样快。它是重要的索引。索引上的 count() 应该很快；)
回答by Jeffrey Kemp
All other things being equal, "select 1 from my_table"will return the firstresult quicker than "select count(*) from my_table", but if you retrieve all the results from the query, the count(*)one will be quicker because it involves much less data (1 integer, as opposed to 1 integer per each row in the table).
在所有其他条件相同的情况下，"select 1 from my_table"将比更快地返回第一个结果"select count(*) from my_table"，但是如果您从查询中检索所有结果，则该结果count(*)会更快，因为它涉及的数据要少得多（1 个整数，而不是每行 1 个整数）桌子）。
回答by Evan Carroll
I don't think this is true for Oracle. http://justoracle.blogspot.com/2006/12/count-vs-count1.html
我不认为这对 Oracle 来说是正确的。 http://justoracle.blogspot.com/2006/12/count-vs-count1.html
But, in some databases the reason is because '*' has to visit the tables meta-data. This tends to add an un-needed overhead. Where as 1 is just a literal.
但是，在某些数据库中，原因是“*”必须访问表元数据。这往往会增加不必要的开销。其中 1 只是一个文字。
