Re: Which is faster?

I recall a statement that the restrictions, that will result in a minimal number of records should be put last. This especially when you don't have the cost based optimizer (V6, V7.0).
So, If table_a.column_1 = table_b.column_1 will result in 100 records, and table_a.column_2 > 10 only 3, use the first option. If the other way around, use the second. And yes, verify, using explain plan. And yes, create indexes if your tables are large (otherwise, a full table scan in memory may be more efficient than an index range scan from disk...)

Anyway, I'm not a DBA ;)

Torben Jeppesen wrote:

> It does not make any difference in this case.
>
> Make shure, however to make Oracle compute statistics on both tables (which
> in turn will force you to compute statistics on ALL tables in) because there
> are several passible access paths and several ways of doing the join.
>
> With computed statistics the optimizer will literally find the best access
> path in 95 % of all cases, and in many cases the SQL will be "rewritten"
> internally to be most cost-efficient independent of the order you write the
> conditions in.
>
> Use EXPLAIN PLAN or TKPROF to evaluate your experiments.
>
> /Torben Jeppesen
>
> wychan wrote in message <3688F473.AB057648_at_vol.net>...
> >Which of the following select statement is running faster:
> >
> >select * from table_a, table_b
> >where table_a.column_1 = table_b.column_1
> >and table_a.column_2 > 10;
> >
> >
> >select * from table_a, table_b
> >where table_a.column_2 > 10
> >and table_a.column_1 = table_b.column_1
> >
> >Or they are homogenous?
> >
> >Thanks for any advice,
> >River
> >novice DBA
> >
Received on Wed Dec 30 1998 - 09:58:59 CET