Hi,

I have the following query:

select * from RANKINGITEM2 RI
join RANKINGCLUB RC on RC.RANKINGCLUBID = RI.RANKINGCLUBID
join RANKING2 R on R.RANKINGID = RC.RANKINGID
join STYLE ST on ST.STYLEID = R.STYLEID
join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID
join CLUB C on SR.CLUBID = C.CLUBID
join CLUB N on N.CLUBID = C.CLUBIDNATION
join MEET M on M.MEETID = SR.MEETID
join CLUB MN on MN.CLUBID = M.NATIONID
join SWIMEVENT SE on SE.SWIMEVENTID = SR.SWIMEVENTID
join ATHLETE A on SR.ATHLETEID = A.ATHLETEID
join CLUB CA on CA.CLUBID = SR.CLUBID_ALT
where
RC.CLUBID = 346 and R.SEASON = 2006
and R.COURSE = 1 and R.GENDER = 1
and R.AGEMIN = -1 and R.AGEMAX = -1
and ST.STROKE = 1
and RI.SORTCODE = 0
order by ST.STYLESORT, RI.SORTCODE

this works fine. Also the plan is the best possible one:

PLAN SORT (JOIN (R INDEX (IX_RANKING_SEASON),ST INDEX (PK_STYLE),RC
INDEX (IX_RANKINGCLUB_RANKING),RI INDEX (PK_RANKINGITEM2),SR INDEX
(PK_SWIMRESULT),CA INDEX (PK_CLUB),C INDEX (PK_CLUB),N INDEX
(PK_CLUB),M INDEX (PK_MEET),MN INDEX (PK_CLUB),SE INDEX
(PK_SWIMEVENT),A INDEX (PK_ATHLETE)))


Now not all records in SWIMRESULT have a reference to ATHLETE. So I
try to "left join" the last two tables (ATHLETE A and CLUB CA).

Unfortunately the plan is completely different and not very efficent
anymore:

PLAN SORT (JOIN (JOIN (JOIN (C NATURAL,N INDEX (PK_CLUB),SR INDEX
(IX_SWIMRESULT_CLUB),SE INDEX (PK_SWIMEVENT),M INDEX (PK_MEET),MN
INDEX (PK_CLUB),RI INDEX (IX_RANKINGITEM_SWIMRESULT2),RC INDEX
(PK_RANKINGCLUB),R INDEX (PK_RANKING2),ST INDEX (PK_STYLE)),A INDEX
(PK_ATHLETE)),CA INDEX (PK_CLUB)))

How can I change the query, that it still uses the first plan?

cu Christian