Consider the following query and notice the CALCULATE_INCENTIVE function:
SELECT EMP.* FROM EMPLOYEES EMPS
WHERE
EMP.STATUS = 1 AND
EMP.HIRE_DATE > TO_DATE('1/1/2010') AND
EMP.FIRST_NAME = 'JOHN' AND
CALCULATE_INCENTIVE(EMP.ID) > 1000
ORDER BY EMPS.ID DESC;
I was under the impression that Oracle uses the same (or similar) short-circuitry that .NET uses in its and/or logic. For example, if EMP.STATUS = 2, it won't bother evaluating the rest of the expression since the entire expression would return false anyway.
In my case, the CALCULATE_INCENTIVE function is being called on every employee in the db rather than only on the 9 records that the first three WHERE expressions return. I've even tried putting parenthesis around the specific expressions that I want to group together for short-circuit evaluation, but I can't figure it out.
Anyone have any ideas how to get the CALCULATE_INCENTIVE not to be evaluated if any of the previous expressions return false?
One way is to put the primary criteria into a subquery that Oracle can't optimize away, then put the secondary criteria into the outer query. The easiest way to ensure that Oracle doesn't optimize out the subquery is to include rownum in the select statement:
SELECT * FROM (
SELECT EMP.*, ROWNUM
FROM EMPLOYEES EMPS
WHERE
EMP.STATUS = 1
AND EMP.HIRE_DATE > TO_DATE('1/1/2010')
AND EMP.FIRST_NAME = 'JOHN')
WHERE CALCULATE_INCENTIVE(ID) > 1000
ORDER BY EMPS.ID DESC;