Re: Oracle Logical Operator order of execution

<vitalisman_at_gmail.com> schreef in bericht news:730ec110-34ea-48c1-b485-d5f97e6b2f01_at_13g2000hsb.googlegroups.com...
> On 1 avr, 21:05, Wally <wallyr..._at_gmail.com> wrote:
>> Oracle 10g R2
>> Win 2003
>>
>> I have a select statement which is similar to
>>
>> select * from test
>> where
>> a = v_number
>> or
>> b between v_date_low and v_date_high;
>>
>> In my database , I know for a fact that only one of these conditions
>> will be true at any given time
>>
>> Can someone tell me if Oracle calculates the left side of the OR
>> operator first or the right side because I don't want Oracle to waste
>> processor cycles calculating one condition when the other is already
>> true.
>
> Are you seriously trying to reduce the CPU bandwidth used by the
> *condition evaluation*? (Or are you rather concerned by access paths?)
> Have you been able to measure this CPU consumption, even with millions
> of rows? Is your query run every second or something?
>
> Whatever, I hope we can trust Oracle in not evaluating an expression
> of an OR-condition for a row after another expression has already been
> evaluated to TRUE. That looks like basic optimization.

That's correct, but unfortunately you can not always predict which clause is executed first..
Sometimes one can have very heavy conditions in the clauses of an and/or. For example in Oracle Spatial, where some calculations can take a lot of time. In those cases, one would like a 'cheap' condition to be calculated first, avoiding the 'expensive' condition be calculated. One example (in pseudo code)
where area1 overlaps with area2
could be replaced by
where (area1 close enough to area2) and (area1 overlaps area2) which would be a lot cheaper to calculate, given one could know the first clause is done first. Unfortunately, the optimizer does not always do well with spatial (at least when I used it some years ago), so other solutions should be used here occasionally (like subqueries).

Shakespeare Received on Thu Apr 03 2008 - 05:11:01 CDT