Re: [Optimization] Early abort in boolean eval when possible?

In the complete absence of other prioritising mechanisms (such as indexes existing on the columns used), Oracle will evaluate the where clause in your example from the bottom upwards. (As at oracle 7.3, I haven't checked it yet for Oracle 8).

The first condition that tests to FALSE will then cause the row to be discarded.

This has two side-effects
a) The order of the where clause can change the performance of a query (though the difference is usually very small)

b) Changing the order of the conditions in a where clause can cause a query to crash out. This is particularly prevalent in designer code where e.g.

    character fields have been used to hold numeric-looking data or lengthy fields have been used to hold structured data

Example:

    where

            character_column = 14
    and date_column = '14-jan-1999'

may work, whereas

    where

            date_column = '14-jan-1999'     and character_column = 14

may fail.

This would happen if every row where
date_column = '14-jan-1999' had a character_column which always looked like a number, but some rows had a character_column that could not be converted to a numeric.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

scott_at_scam.XCF.Berkeley.EDU wrote in message <7ddtjh$cs7$1_at_agate.berkeley.edu>...
>
>Does Oracle support an optimization where boolean expressions are aborted
>when further evaluation would not alter the outcome?
>
>In other words, which of these selects is faster?:
>
>1) select * from employees where
> evaluation like '%recommend termination%' or
> salary > 100000
>
>2) select * from employees where
> salary > 100000 or
> evaluation like '%recommend termination%'
>
Received on Thu Mar 25 1999 - 15:29:21 CST