Hi Christian!

An inline select will always return 0 or 1 row, whereas a join
(whatever type) could potentially return several rows from both tables
(although, in theory, it could check whether the join clause was on a
unique or primary key and discover whether the ). The ordering is on
the result set, and for your inline select this is bound to contain as
many rows as the Lademittel table, whereas the LEFT JOIN possibly
could contain more rows. Hence, Arno (or some optimizer before him)
figured that the index could be used for ordering in the first case,
but not in the second.

Have you checked how long time it takes to return the entire return
set, and not simply just the first record(s)? If that difference is as
big as you report, then it is truly a significant (magnificent has a
slightly different meaning!) difference. In general, if you do not
include any where clause in a joined dataset, you have to live with
one (or more) of the tables being accessed without an index - simply
because it is faster.

Set

--- In firebird-support@yahoogroups.com, "Christian" wrote:
> Hi all,
> these two calls display the same results, the inline-select after a
> few ms, the join after 7 sec.
> What is the reason for this magnificant difference?
> Why is the plan of the join natural und not by IX_LADEMITTEL_LM_NR?
>
> Greetings Christian
>
>
> SELECT
> L.*,
> (SELECT D.DO_NR FROM DOSSIER D WHERE D.DO_ID = L.DO_ID) AS DOSSIER
> FROM LADEMITTEL L
> ORDER BY L.LM_NR
>
> ------------------------------------
> PLAN (L ORDER IX_LADEMITTEL_LM_NR)
> PLAN (D INDEX (PK_DOSSIER))
>
> ------------------------------------
> Indexed Read:
> Dossier:93
> Lademittel:96
>
> NonIndexed: 0
> ------------------------------------
>
> SELECT
> L.*,
> D.DO_NR
> FROM LADEMITTEL L
> left JOIN DOSSIER D ON L.DO_ID = D.DO_ID
> ORDER BY L.LM_NR
>
>
> ------------------------------------
> PLAN SORT (JOIN (L NATURAL,D INDEX (PK_DOSSIER)))
>
>
> Indexed Read:
> Dossier:112.755
>
> NonIndexed:
> Lademittel:112.791