Why is Select 1 faster than Select count(*)?
Solution 1
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 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.
(Note: I wish I could post this as a comment on CodeByMidnight's post, but comments can't include formatted code).
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(*)
.
Solution 2
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.
Solution 3
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.
Related videos on Youtube
Admin
Updated on April 19, 2022Comments
-
Admin about 2 years
In Oracle, when querying for row existence, why is Select 1 fast than Select count(*)?
-
Glen over 14 yearswithout knowing which RDBMS engine you're using there's no way to answer correctly. Different engines behave differently
-
Tony Andrews over 14 yearsDo you mean "why is select count(1) faster than Select count(*)"?
-
E_the_Shy over 14 yearsWhich question can you answer fastest. (a) Is there someone called "Smith" in the phone book? (b) How many called Smith are there in the phone book?
-
-
APC over 14 years+1 We should only use COUNT() we need to know the actual number of records involved.
-
Jeffrey Kemp over 14 yearsyes. Oracle treats count(*) exactly the same as count(1), count(null), count('any atomic value you like').