Re: SQL Tuning question

Dan wrote:
> I'm trying to put together some general 'best practices' for writing
> sql queries. I've been reading several books, docs, etc. One thing
> I've read very little about, but have heard about, is the order of
> filters in the 'where' clause and whether a field is on the right side
> or left side of the equal sign.

<snipped>

Dan, in my experience this very seldom makes a difference these days with the Oracle CBO. In the past, this somestimes an issue - methinks primarily because the join order was at times decided based on predicate assignment direction. Driving the join from the larger result set is always bad mojo. Recall resorting to the ORDERED hint numerous times in the past (Oracle 7.1 days) to get the join order in the correct sequence.

What is important in the predicate clause is using functions on the right operand. E.g. not doing this:
WHERE TO_CHAR( date_colum, 'yyyy/mm/dd' ) = '2000/01/01' but this:
WHERE date_colum = TO_DATE('2000/01/01', 'yyyy/mm/dd')

What makes this a stinger at times is that developers code it as: WHERE date_colum = '2000/01/01'

..not realising that they are forcing an implicit TO_CHAR conversion on the data column for the condition to be evaluated correctly.

Oh yeah - usual disclaimers apply as I'm not using the "new" join syntax. I dislike it. And I have seen -numerous- occassions where this so-called natural/logical join syntax cause developers to write the absolutely worse performing SQL that I've ever seen. Because of getting the predicate (with join clauses) wrong.

--
Billy