Re: Affect of table name order in SQL

Mike writes in article <DE8LyD.7A7_at_HAN.UnisysGSG.COM>:
>
> We have been tring to optimize some SQL statements and discovered that
> the order of the table names in the FROM clause affected the query time.
> I thought that Oracle would determine the best way to do the query;
> freeing us from deciding how to write the SQL statement. Has anyone
> else run across this problem?
>
> We are running Oracle 7.1 on an N.T. platform.
>
> Thanks
> prompom_at_mcln.unisysgsg.com
>

If several access paths thru different tables are possible (for example, a unique index on the columns for every table exists), then the table which is the last one in the FROM clause is used first. This behaviour is documented.

As a general rule, use the table where you expect the least number of records returned as the last one in the FROM clause. And, of course, check with EXPLAIN PLAN and/or TKPROF....

Willy Klotz  

>

Willys Mail     FidoNet              2:2474/117  2:2474/118    
      willyk_at_kbigate.stgt.sub.org      VFC        ISDN/X.75