Re: Order of where clause matters!

Review Oracle's documentation on operator precedence.

In your example, the divide operation has the highest precedence & Oracle will try to evaluate it first, regardless of its location in the where clause. This is not dependent on the version of Oracle in case it matters. Operator precedence has been consistent across versions or at least versions 7 & 8.. The random element can be attributed to the optimizer. Your uncontrolled divide was a bug waiting to bite.

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.

The use of a function on an indexed item will discourage the use of the index. Functions like substr, to_char are definitely killers. I'm not so sure about decode, so check the plan with and without the decode function.

Steve Haynes wrote:

> Hi All,
>
> RE: 8.0.5.1.1 on NT4.
>
> I have a strange problem with a divide
> by zero error...
>
> select whatever
> from my_table
> where stuff /* stuff that means "col_a" is never 0 */
> and col_b/col_a > 10;
>
> This gave me a divide by zero error...
> I then changed the order of the where clauses
> so that "and col_b/col_a > 10" was first after the
> "where" keyword and the error went away.
>
> I then added an index and the error came back.
> Drop the index, error goes away.
>
> This is a new one on me. Two issues here, one
> is the order of the where clause, and one is
> the existence of the index (altering the plan).
>
> Is there a way I can predict this behaviour?
>
> I need the index but I don't need the error!
>
> Thanks,
>
> Steve
Received on Tue May 30 2000 - 00:00:00 CDT