I've come across coding that is using a 1=1 conditional aggregate instead of a count
select sum(case when 1=1 then 1 else 0 end)
In my eyes, this is equivalent to:
select count(1)
At first I figured this was a place holder where the developer had intended to revisit with some additional logic in this conditional aggregate, but then I found it in another script, and then another. This prompted me to ask around and I discovered a previous developer had stressed that this sum condition is in all cases more efficient and faster than a count function would ever be (stressed it well enough that other developers followed the standard later on). Seems quite counter intuitive to me, forcing the system to evaluate a 1=1 condition might be minor, but it's still additional work over the count function. Figured I'd consult on here before I came back with a firm statement.
a) is there any truth to what this developer was saying the conditional aggregate will be faster than the count
completely side question: b) was there ever a db system that would evaluate the condition aggregate quicker than a count?
This is an oracle 11g database, though I suspect the scripts were written for oracle 8i
And as bonus points...I've been asked to optimize this code. Will removing replacing this with a count function improve speed at all? Record counts exceed 100 million at times.
Executive summary: it makes no difference and it never has in Oracle, since at least version 6 (1989), which was when I first started hearing about clever ways to count faster by selecting primary key columns etc, as if Oracle was not aware that people sometimes count things.
You can see what the parser/optimiser does with the expression by using it in a filter and checking the 'Predicates' section of the execution plan.
Normal
count(*)
(Oracle 12.1):Clever superfast expression:
Notice the Predicates section, which shows that the
sum
expression has been evaluated and replaced withsum(1)
. (I don't have time to dig into tracefiles right now, but I am pretty sure they would show that the rewrite occurred before CBO optimisation.)Here's what it does with
count(1)
, another expression sometimes thought to be more efficient than the standard one:And here's the plan without filters:
As you can see, they are all the same (apart from differences in my artificial filter condition).
Also,
sum(1)
does not give the same results ascount(*)
when there are no rows: