Re: table joins or separate begin-select clause

[Albert Howard <alhoward@concentric.net>]

If all other ranks are equal, the order in the FROM clause should be
irrelevant.  The real important factor is how the WHERE clause is coded.
Are the keys specified, and in proper order?  If not, you may not get
efficient use of available indices.  This can have a tremendous effect
on performance.

Obviously, you can not always join by keys (which is the beauty of SQL,
that you don't have to), but its best to do so when you can.


Sofia Dobkin wrote:
>
> I belive you are right, but only for ORACLE V6, in version 7
> ORACLE will use tables in order they are listed in the FROM
> clause if all ranks are equal.
>     Sofia Dobkin
>
> >----------
> >From:  Lydia Verhoef[SMTP:lverhoef@ZEUS.CC.PCC.EDU]
> >Sent:  Wednesday, September 25, 1996 1:51 PM
> >To:    Multiple recipients of list SQR-USERS
> >Subject:       Re: table joins or separate begin-select clause
> >
> >I don't know if there is a general rule for this across all databases
> >but for the Oracle database Ben Le is correct.  Large tables with the
> >smallest number of qualified rows should be listed last in the FROM
> >clause.    The optimizer first tries to rank all aspects of the query,
> >if all ranks are equal then Oracle uses the last table in the FROM
> >clause as the driving table.
> >
> >Also, you will want to list the join clause for the pair of tables
> >resulting in the smallest result set last in the list of join clauses.
> >
> >Hope this helps,
> >Lydia
> >