Re: Order of where clause matters!

Steve Haynes wrote in message ...
>
>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've hit this problem before in earlier releases of Oracle. Basically I think the order in which the WHERE clauses which are just simple filter conditions are executed is undefined, and will vary depending on how the optimiser parses the query. Under the rule based optimiser it was possible to predict in which order the clauses would be executed, but this is less so with the CBO. Adding indexes can certainly change the entire execution plan of the statement (as I'm sure you know). And besides, I don't think Oracle will guarantee that even if you work out it's heuristics for this version, that they'll remain the same for the next.

The safest rule is not to make your where conditions interdependent - don't assume that one condition will be performed before another.

I like the

wliu2> select *
wliu2> from my_table
wliu2> where stuff /* stuff that means "col_a" is never 0 */
wliu2> and   col_b/decode(col_a, 0, 1, col_a) > 10;

solution, as it won't disturb your execution plan, whereas the subquery solution complicates the execution plan from the optimiser's point of view and perhaps constrains it to be parsed in a particular (possibly suboptimal) way. Probably not a problem with your simple example, but more of concern with real-life queries with multiple joins.

You said (in another posting to the thread):

> However it does worry me that adding indexes in the future could break
> other existing code.

I'd suggest that the code is already broken! But I agree that very little Oracle or third-party documentation alerts you to these potential problems.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.