Re: Oracle Performance

> >
> > These 2 tables are joined with the following where and order by clauses
> > where T1.fielda = T2.fielda
> > and T1.fieldb = T2.fieldb
> > and T1.fieldc = T2.fieldc
> > and T1.fieldb >= '[a start date]'
> > and T1.fieldb <= '[an end date]'
> > order by T2.fielda, T2.fieldb, T2.fieldc, T2.fieldd
> >
>

Well, if you take a rules-based approach, you would start by putting the most fine-grained relationship LAST in your 'where' clause and working your way up from there, e.g.,

		 T1.fieldb >= '[a start date]'
                 and T1.fieldb <= '[an end date]'
 		 and T1.fielda = T2.fielda
                 and T1.fieldb = T2.fieldb
                 and T1.fieldc = T2.fieldc

The order of the last three lines above would depend on which of the relations will return the least amount of rows to further filter (you can figure that out with a few simple queries). The finest filter goes last, next-finest filter above that, etc.

(BTW, I assume you have checked explain plan?)

hth -

Regards,
jh

--
Jared Hecker          | DB Consulting (modelling and DBA, Oracle/Sybase)
hecker_at_toysrus.com   |  on assignment
jared_at_hwai.com        |  back at the ranch...