Hi,

Beside what Dmitry ans Set answers :

> 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

Please use alias everywhere when you add alias to table.
Now i've to guess where RANKINGID, PLACE1 etc... is coming from.

> 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)))

As Dmitry explained a ORDER BY can currently not be used when a OUTER JOIN
is part of the query.
LEFT is a OUTER JOIN as RIGHT and FULL.

If you need a ORDER by index you can turn your LEFT JOIN into a sub-query
(ofcourse only when 1 or none match is made).

select
first 25 skip 0 FIRSTNAME, LASTNAME, BIRTHDATE,
GENDER, C.CODE, SWIMTIME, PLACE1,
M.CITY as MEETCITY, M.STARTDATE as MEETDATE,
(SELECT MN.CODE FROM CLUB MN WHERE
MN.CLUBID = M.NATIONID) 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
where
RANKINGID = 1009658 and
not (PLACE1 is null)
order by
RANKINGID, PLACE1

Where is PLACE1 coming from and what datatype does it have?
How many PLACE1 records have the state NULL?

> 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).

Of course the optimizer should do good work and when it doesn't it needs to
be fixed, but if you want something specific in your own order. It's
probably the best to write a selectable stored procedure.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info