There is a small but measureable difference and on the system I measured
summing nulls rather than zeros was faster.
For a billion rows null took about 2:38 (min:seconds) and zero took about
2:45 with plus minus variation of about 1 second.
Your mileage may vary and I did not trace the internals details.
select sum(decode(rownum,1,1,0)) from big_table where rownum < 1000000000;
versus
select sum(decode(rownum,1,1,null)) from big_table where rownum <
1000000000;
both returned 1 row reporting 1 (only rownum 1 returns other than zero or
null).
If any of all y'all see different results on the timing, please let us know.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Wednesday, June 07, 2017 3:25 PM
To: 'ORACLE-L'
Subject: sum of nulls versus zeroes
sum() as defined by oracle ignores nulls.
It has been at least since Oracle 6 that I measured which was faster: sum()
with a bunch of zeroes or sum() with a bunch of nulls (each with some
non-zero values as well).
So I have some existing sqls that have to be modified. Has anyone tested
whether either is significantly faster?
An existing case statement produces a null that could just as easily be a
zero.
If someone else has tested recently, then I get to be lazy and leverage the
list.
Otherwise I'll break down and do the work. I suspect it doesn't matter, but
there might be 100s of millions of null or zero rows in the sum, so a small
difference could matter.
Thanks in advance if you have the answer for 12.1 or 12.2 in hand.
No need to comment if you don't have the answer. And no, I can just skip the
rows, multiple different columns are being summed.
mwf
