Re: SQL Question !?

Joost Ouwerkerk wrote:
>
> I can't seem to find any documentation about this, so I'm hoping
> someone will be able to help me:
>
> What is the logic for determining what is the ideal order for listing
> table names after the FROM clause in a SELECT statement? I didn't
> think there was one, until I discovered significant performance
> enhancements simply by placing one table name before another. It's
> pretty much trial and error, though. What's the deal?
>
> eg. SELECT x, y FROM a, b WHERE x=y; might be significantly faster
> than:
> SELECT x,y FROM b, a WHERE x=y;
>
> Anyone?
>
> Joost Ouwerkerk
> HSC Foundation,
> Toronto, Ontario, Canada.

Oracle appears to access the right most table in the FROM statement first,
as well as the right most join in the WHERE statement. What I have found is
that I put my driving table (and join) which is using indexed columns to the
right. Then those tables where the columns may not be indexed as far to the left
as possible.

Basically, I trim my data down using the indexes, from right to left, with the
left most columns being the ones that would normally take the most time.

I had one SELECT statement that went from 35 minutes, down to 10 minutes, just by
moving these tables and join statements around like this.

Hope this helps. Received on Thu May 29 1997 - 00:00:00 CDT