Re: SQL Tuning question

"Dan" <daniel.ostertag_at_visaer.com> 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.
>
>For example, in a query such as
>select a.xxx, b.yyy, c.zzz from table1 a, table2 b, table3 c
>where
> a.aaa = b.bbb and
> b.aaa = c.ccc and
> b.ddd = 'XXX';
>
>I've read about using the smallest table as the driving table, but
>other than that, the order of the tables or whether b.aaa=c.ccc or
>c.ccc=b.aaa is correct, I can find very little about.
>
>Does anyone have any info or general rules on this or a reference as to
>where I could read more (or perhaps tell me it's not important).
>
>Thanks,
>Dan

In general ( and in theory), on an analyzed table(s), The Oracle RBO will find the best method, regardless of the order..

( as an aside,iirc, it is not the 'smallest table' that is always best as the 'driving' one, but the table who's returned rows can be reduced as far as possible by the selection criteria..This reduces the # of rows read from the linked tables..) Received on Wed Oct 19 2005 - 15:01:23 CDT