RE: sum of nulls versus zeroes

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l