Dmitry knows the internals of Firebird better than any person who's
never written the code, so his answer is most certainly correct and
complete. However, some of us that don't understand the internals are
using various tricks to fool Firebird into choosing a different plan.
There are basically two different indexes that your plan uses with the
second plan that it doesn't use with the first:

IX_RANKINGITEM_RESULTID and IX_SWIMRESULT_ATHLETE

Trying to eliminate these from being used, may give Firebird the clues
it needs to select your favourite plan, i.e. try either or both of

/* eliminates IX_RANKINGITEM_RESULTID */
join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID+0

/* eliminates IX_SWIMRESULT_ATHLETE */
join ATHLETE A on SR.ATHLETEID+0 = A.ATHLETEID

The latter one is the most certain candidate for choosing a different
plan (since RANKINGITEM uses two indexes it could simply remove
IX_RANKINGITEM_RESULTID, leaving you with only a marginally better
plan).

HTH,
Set

--- In firebird-support@yahoogroups.com, Christian Kaufmann wrote:
> Hi,
>
> I already came up with the following query on August 4, 2004 and I
> thought, the problem was solved. But now I added a table with a left
> join and the plan is wrong again:
>
> The following statement works ok:
>
> select first 25 skip 0 FIRSTNAME, LASTNAME, BIRTHDATE,
> GENDER, C.CODE, SWIMTIME, PLACE1,
> M.CITY as MEETCITY, M.STARTDATE as MEETDATE
> from RANKINGITEM RI
> join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID
> join ATHLETE A on SR.ATHLETEID = A.ATHLETEID
> join CLUB C on SR.CLUBID = C.CLUBID
> join MEET M on M.MEETID = SR.MEETID
> where RANKINGID = 1009658 and not (PLACE1 is null)
> order by RANKINGID, PLACE1
>
> the plan is:
> PLAN JOIN (RI ORDER IX_RANKINGITEM_PLACE1,SR INDEX (PK_SWIMRESULT),M
> INDEX (PK_MEET),A INDEX (PK_ATHLETE),C INDEX (PK_CLUB))
>
> now I add a left join with another CLUB table:
>
> select first 25 skip 0 FIRSTNAME, LASTNAME, BIRTHDATE,
> GENDER, C.CODE, SWIMTIME, PLACE1,
> M.CITY as MEETCITY, M.STARTDATE as MEETDATE,
> MN.CODE as MEETNATION
> from RANKINGITEM RI
> join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID
> join ATHLETE A on SR.ATHLETEID = A.ATHLETEID
> join CLUB C on SR.CLUBID = C.CLUBID
> join MEET M on M.MEETID = SR.MEETID
> left join CLUB MN on MN.CLUBID = M.NATIONID
> where RANKINGID = 1009658 and not (PLACE1 is null)
> order by RANKINGID, PLACE1
>
> the plan then is:
> PLAN SORT (JOIN (JOIN (A NATURAL,SR INDEX (IX_SWIMRESULT_ATHLETE),M
> INDEX (PK_MEET),C INDEX (PK_CLUB),RI INDEX
> (IX_RANKINGITEM_RESULTID,IX_RANKINGITEM_PLACE1)),
> MN INDEX (PK_CLUB)))
>
> I don't know how to force the optimizer to search on RANKINGITEM
> with IX_RANKINGITEM_PLACE1 first. I tried to use the plan from the
> first query, but I get an error "index IX_RANKINGITEM_PLACE1 cannot
> be used in the specified plan"
>
> Since in RANKINGITEM I have the 25 records I want right together
> with an index, I don't understand, why the optimizer starts with a
> natural search on the ATHLETE table (15'000 rows).
>
> cu Christian