Re: Oracle Logical Operator order of execution

On Apr 1, 3:05 pm, 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. I can change the query to the one below if need be
>
> select * from test
> where
> b < v_date
> or
> a = v_num;
>
> Thanks in advance.

Oracle's cost based optimizer may not look at both sides of an or and there is no guarantee that it will always start on one side or the other. Similarly with an "and" condition it may use one side or another to use indexes and then eliminate rows after they are returned as possible hits.

If you are running a recent release you may want to get familiar with dbms_xplan.display Received on Tue Apr 01 2008 - 14:32:55 CDT