Arithmetic

Arithmetic:
Here’s an amusing little question that appeared on OTN a short while ago:

From the performance point of view, which of the following is better and why :

(1) select FILE_NAME, TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 from dba_data_files

(2)  select FILE_NAME, TABLESPACE_NAME, SUM(BYTES)/(1024*1024*1024) from dba_data_files



Assuming we add the necessary “group by” clause to the end of the queries, how could we find out if there is any difference (other than testing the queries on a system with a very large number of data files to see if we can spot a difference in the CPU usage caused the by change in the arithmetic expression ?
We could check to see if dbms_xplan gives us any clues – perhaps we would see some difference in the “projection” section of the output. (We don’t.)
How about checking the cpu_cost column from the plan table for the various steps of the two different plans – they may differ. (They dont’.)
But there probably is a difference (at least on 11.1.0.7, which is the version I happened to have in front of me at the time), and we can get a clue about it if we change the query a little. Try the following:

explain plan for
select 
 file_name, tablespace_name, sum(bytes)/(1024*1024*1024) 
from 
 dba_data_files 
group by 
 file_name, tablespace_name
having 
 sum(bytes)/(1024*1024*1024) > 0
;

select * from table(dbms_xplan.display);

Note that I’ve added a having clause that uses the same expression that I used in the select list. Here’s how it reappears in the predicate section of the output – Oracle has precalculated the result of the multiplication steps:

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("BYTES")/1073741824>0)

If we try the same trick with the alternative expresion, we get the following – Oracle hasn’t been able to do the same type of manipulation:

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("BYTES")/1024/1024/1024>0)

I wouldn’t take this as conclusive, of course, but it looks as if Oracle may have to perform three separate division operations for every single row returned in the second case but just one division per row in the first which might, therefore, use a little less CPU and be a little more efficient.
As a (slightly) corroborative piece of evidence, if you include both predicates in the having clause then the optimizer isn’t able to recognise that they are identical and eliminate one of them. Here’s the resulting predicate:

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("BYTES")/1073741824>0 AND SUM("BYTES")/1024/1024/1024>0)

DIGITAL JUICE