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