Christian Kaufmann wrote:

> 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

Easy Christian - you can't ;o)

Though of course there is a longer answer that hopefully will help you
get a better plan eventually.

The problem isn't in your ATHLETE table, I would expect

...
join CLUB CA on CA.CLUBID = SR.CLUBID_ALT
left join ATHLETE A on SR.ATHLETEID = A.ATHLETEID
WHERE ...

to give you a plan very similar to your original preferred plan. ATHLETE
is already the last table in your plan and anything that you left join
to has to be put after inner joins.

However, CLUB CA appears pretty early in your plan, and LEFT JOINing to
this one will change your plan. But even though your original plan isn't
possible anymore, you can prevent the optimizer from choosing its
suboptimal plan. My first thought was to prevent the optimizer from
choosing the index on N.CLUBID though

...//Don't do this
join CLUB N on N.CLUBID+0 = C.CLUBIDNATION
...//Don't do this

But a closer examination shows that this index is also used in your
preferred plan. Hence, this isn't the problem and we don't want to do that.

The next part of your suboptimal plan is
SR INDEX(IX_SWIMRESULT_CLUB)

This index isn't at all used in your preferred plan, so preventing this
may help the optimizer do some right choices:

...
join CLUB C on SR.CLUBID+0 = C.CLUBID
...

This doesn't guarantee that the optimizer gets things right (even though
I expect it to be enough), but it does prevent its first suboptimal
attempt. Report back to the list what the plan is with this additional
+0, and if it still causes problems for you.

HTH,
Set