Where clause ordering

Here's a quirky little example of optimiser implementation I came across today.

As you probably know, all other things being equal the predicates in a WHERE clause for a table are evaluated from bottom to top, so:

	select count(*) from lookups
	where
		class = 123
	and	to_number(value) = 0;

	select count(*) from lookups
	where
		to_number(value) = 0;
	and	class = 123

depending on which column satisfies the condition least often, and whereabouts in the table the columns are.

In practice, I have never found any real difference in performance even when rigging the data in an extreme fashion, but today I actually ran a statement of the above type: surprise, surprise, the top one would _not_ run (Oracle error: invalid number) whilst the bottom one would.

The reason, if you haven't already guessed it: CLASS = 123 guaranteed that the varchar2 VALUE was really numeric in form so the to-number() was always valid, whereas lots of other rows in the table had VALUE of a non-numeric form.

Intersetingly, changing the query to:

	select count(*) from lookups
	where
		to_number(value) = 0;
	and	class in (123,456)

was sufficient to change the predicate precedence and cause the query to fail again.

Oracle: 7.1.6, on Sequent, SQL*Plus 3.1.3

-- 
Jonathan Lewis