Early abort in boolean eval when possible?

Early abort in boolean eval when possible?

Post by sc.. » Fri, 26 Mar 1999 04:00:00



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%'

In these examples, the where clause should stop evaluating the
boolean expression as soon as further evaluation cannot change the
result.  For example, in the expression (A or B), B never needs to
be evaluated if A is found to be true.

If this is the case with Oracle, then in the above two examples,
#2 should turn out to be faster because some fraction of the where
clause expressions would cease before performing the second (slow)
term.

However, this presumes that the compiler preserves the order of
the subexpressions.  Compilers don't always guarantee order
preservation.  Some might even try to be so clever as to start
multiple simultaneous threads (one for each subexpression) and
treat the entire expression as a race condition.

So, how exactly does Oracle handle this?  Is it the same for all
operating systems?

Thanks for the help.  I'm sure a lot of people would be interrested
in this question if they don't already know the answer, so please
post here.

Thanks,

Scott

 
 
 

Early abort in boolean eval when possible?

Post by Jonathan Lewi » Fri, 26 Mar 1999 04:00:00


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


Quote:

>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%'


 
 
 

1. Perl won't eval PgSQL boolean value

I've got an if statement that checks if a boolean value is true:

    if ($cust_data->{'hold'} eq 't')

But perl will not evaluate the value. $cust_data->{'hold'} is taken from a
PgSQL boolean field (either t or f). If I use the construct above it tell me
that 'eq' is not defined thinking it's a string.

If I use:
if ($cust_data->{'hold'} == 't')

I get an error saying $cust_data->{'hold'} is a non-numeric value.

If I do this:
 if ($cust_data->{'hold'})

it will always evaluate to true whether the value is 't' or 'f'.

HELP. How do you evaluate boolean values from PgSQL in Perl?

--
Randy Perry
sysTame
Mac Consulting/Sales

phn                 561.589.6449

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. Does any one know how to modify the thesaurus used by MSSearch?

3. Eval Copy on XP: Cannot logon or Connect to Eval SQL Server 2000

4. Transfer

5. KDD-2001 Last Call for Early Registration

6. Column Size

7. Earlier ODBC Drvers please

8. Bug in 7.2.1? -- ERROR: Adding columns with defaults is not implemented.

9. Package reverts to earlier (and non-existent) version

10. DTS SP2 compatibility with SP1 or earlier

11. Installing an earlier version of MDAC

12. Filtering records by the earliest date value

13. ORDER BY earliest date in a row