Re: Order of where clause matters!

Evan wrote in message <39335F43.94ABC781_at_cadvision.com>...

>Control the precedence with brackets. Something like 'where (stuff) /*
>stuff that means "col_a" is never 0 */' will force Oracle to
>evaluate stuff before evaluating the rest of the where clause as items
>in brackets have higher precedence then items not in brackets. Once this
>part of the evaluation fails, Oracle will not proceed to the divide.
>Another alternative is to put both in brackets as in: where (stuff) and
>( col_b/col_a > 10). The 2 bracketed items have equal precedence & stuff
>will be evaluated first (ie left to right order). The optimizer cannot
>override the brackets.

Sorry, not true. The documentation about operator precedence is possibly misleading.

Although the Oracle7 docuementation says:

    "You can use parentheses in an expression to override operator

     precedence. Oracle7 evaluates expressions inside parentheses before
     evaluating those outside. "

(and no doubt the Oracle8 docs say the same), all this really means is that "a AND (b OR c)" is evaluated differently to "a AND b OR c", which is equivalent to "(a AND b) OR c".

It does not mean that "(a) AND b" forces a to be evaluated before b. Neither does the fact that "/" has a higher precedence than AND mean that expressions with a division operand are naturally evaluated before conditions without, when they're logically combined with other expressions using AND and OR.

The following examples demonstrate:

   SQL> create table dual2 (a number);
   SQL> insert into dual2 values (0);
   SQL> insert into dual2 values (1);

   SQL> select 1 from dual2
        where a != 0 and 7/a = 4;

   ERROR:
     ORA-01476: divisor is equal to zero

   SQL> select 1 from dual2
        where (a != 0) and 7/a = 4;

   ERROR:
     ORA-01476: divisor is equal to zero

   SQL> select 1 from dual2
        where ( (a != 0) and 7/a = 4 );

   ERROR:
     ORA-01476: divisor is equal to zero

but

   SQL> select 1 from dual2

        where 7/a = 4 and a != 0;

   no rows selected

So, *in this version of the database* and *this particular statement, evaluated by this variant of the optimiser*, the second operand of the AND clause is evaluated before the first, regardless of the placing of brackets. This does not imply anything about how other statements are evaluated by this, or any other version of the optimiser.

Dave. Received on Tue May 30 2000 - 00:00:00 CDT