"Christian Kaufmann" <ch.kaufmann@...> wrote:

>
> 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:
>
> [snip]
>
> 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).

There are two problems here.

1) Outer joins cannot use ORDER plan. And you cannot force using ORDER even
via an explicit plan. So your query should be very slow, because the entire
resultset must be externally sorted before fetching first 25 rows. If
RANKINGITEM in fact contains 25 rows, then your query will be quite fast.

2) Optimizer ignores RANKINGID = 1009658 condition when choosing a join
order. Hence it considers ATHLETE to contain less rows. This happens only
with outer joins. This issue can be solved with explicit plan:

PLAN SORT (JOIN (JOIN (RI INDEX (IX_RANKINGITEM_PLACE1), SR INDEX
(PK_SWIMRESULT), M INDEX (PK_MEET), A INDEX (PK_ATHLETE), C INDEX
(PK_CLUB)), MN INDEX (PK_CLUB)))


Dmitry