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?

Hi Joost, let me try an answer:

in the logic of these two statements above there is NO difference. But in the performance there is one, because under ORACLE its faster, to access the table first which delivers MORE data than the other to the query or has MORE indexes on the selected columns. Both optimizers (rule-based and cost-based) depend on at least on of this
facts in the preformance to which they convert the query. One tip: look at the execution plan of both statements. Maybe you can see
differences their.
If a statement really has to be fast, you first should compare different variations of it in their execution-plans.

You can find documentation on it in the

        Oracle7 Server Tuning Guide (in the Oracle Documentation Lib)

Hope it helps ?

Greetings

-- 

Dipl.-Inform. Olaf Musch

GZS mbH Gesellschaft für Zahlungssysteme
Unternehmensbereich Processing
Theodor-Heuß-Allee 80
60486 Frankfurt

mailto:Olaf.Musch_at_gzs.de
------------------------------------------------------------
http://www.gzs.de
http://www.eurocard.de