| Subject | Re: [firebird-support] Problem with PLAN in query with left join |
|---|---|
| Author | Svein Erling Tysvaer |
| Post date | 2006-09-27T21:42:57Z |
Christian Kaufmann wrote:
R.RANKINGID+0, so adding +0 basically tells the optimizer that this
index is unavailable in this case.
prevent the optimizer from ever changing to another plan. If you defined
another index, it couldn't use it, if you deleted an index, you would
get an error, if you decided to change to LEFT JOIN of another table,
odds are that the plan cannot be used anymore etc. Hence, taking the
softer route of preventing the optimizer from using certain indexes
through "+0" or "||''" (for char or varchar) is what I recommend. And I
haven't heard anyone recommend writing the plan explicitly.
HTH,
Set
>> The problem isn't in your ATHLETE table, I would expectYou have an index on R.RANKINGID, but there doesn't exist any index on
>> join CLUB CA on CA.CLUBID = SR.CLUBID_ALT
>> left join ATHLETE A on SR.ATHLETEID = A.ATHLETEID
>> WHERE ...
>
> no. I did:
> left join ATHLETE A on SR.ATHLETEID = A.ATHLETEID
> left join CLUB CA on CA.CLUBID = SR.CLUBID_ALT
>
> CLUB is joined several times for different purposes.
>
> The first plan is more or less perfect. And for my understanding it
> could be used with the left join's too.
>
>> attempt. Report back to the list what the plan is with this
>> additional +0, and if it still causes problems for you.
>
> I never used such +0. So I played arround and like this it works with
> left join:
>
> join RANKINGCLUB RC on RC.RANKINGCLUBID+0 = RI.RANKINGCLUBID
> join RANKING2 R on R.RANKINGID+0 = RC.RANKINGID
>
> So it works fine now, but what exactly is the influence of +0 ?
R.RANKINGID+0, so adding +0 basically tells the optimizer that this
index is unavailable in this case.
> The plan now is:Well, you COULD write the plan at the end of your query, but that would
>
> PLAN SORT (JOIN (JOIN (JOIN (R INDEX (IX_RANKING_SEASON),ST INDEX
> (PK_STYLE),RC INDEX (IX_RANKINGCLUB_RANKING),RI INDEX
> (PK_RANKINGITEM2),SR INDEX (PK_SWIMRESULT),SE INDEX (PK_SWIMEVENT),C
> INDEX (PK_CLUB),N INDEX (PK_CLUB),M INDEX (PK_MEET),MN INDEX
> (PK_CLUB)),A INDEX (PK_ATHLETE)),CA INDEX (PK_CLUB)))
>
> Is there a way I could force this plan or is adding these +0 an
> offical way to force certain plans?
prevent the optimizer from ever changing to another plan. If you defined
another index, it couldn't use it, if you deleted an index, you would
get an error, if you decided to change to LEFT JOIN of another table,
odds are that the plan cannot be used anymore etc. Hence, taking the
softer route of preventing the optimizer from using certain indexes
through "+0" or "||''" (for char or varchar) is what I recommend. And I
haven't heard anyone recommend writing the plan explicitly.
HTH,
Set