optimizing the sql query by not repeating the math..

Question

Here is the query in Oracle I was trying to remove the redundant math operation from:

SELECT name,

     CASE 
       when nvl(num1,0) + nvl(num2,0) - nvl(num3,0) > 0
       THEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0)
       ELSE 0
      END
      as result,
      .... from ....

How do I not repeat the summation above?

The 'result' should contain - value of the above expression if > 0

                    -  0 if the value of expression is <= 0.

Answer 1

Use a derived table:

select name, nvl(CASE when sumall > 0 THEN sumall end, 0) as result
from (
    select nvl(num1,0) + nvl(num2,0) - nvl(num3,0) sumall, name
    from MyTable
) a

Answer 2

Assuming you want to get rid of the math for performance reasons, the best way to do this (from a DBA's point of view) is to create another column called result, then use an insert/update trigger to automatically populate it when a row changes, with the value you want:

CASE 
   WHEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0) > 0
   THEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0)
   ELSE 0
END

That way, the calculation is only done when required (on a row create/change) rather than on every single read. This amortises the cost of the operation on databases that are read more often than written (which, in my experience, is close to all of them).

Then your query becomes a simple and blindingly fast:

select name, result from ...

This method works because:

• the vast majority of databases are indeed read far more often than they're written; and
• disk space is cheaper than CPU grunt (compare the number of database questions regarding database performance against those regarding storage requirements - the former greatly outweigh the latter).
• database queries with per-row functions rarely scale well as the tables get bigger.

This is all assuming Oracle has triggers of course. I'm a DB2 man myself but I'd be surprised if Oracle was so brain dead it didn't have them. Then again, for all I know, maybe Oracle still can't distinguish between NULL and an empty string, so who knows? :-)

Answer 3

In this case you could replace your CASE expression with:

GREATEST( nvl(num1,0) + nvl(num2,0) - nvl(num3,0), 0 )

OrbMan's answer is more generally useful though.