What happens if you try

Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
join ETABLISSEMENT A on (A.NUM_ETAB+0 = C.NUM_ETAB)
where ((A.NUM_ETAB = 5298)
or (A.CODE_NAF = '5299')) and (C.ID_EFFECTIF <> 'T')

I think that should be a strong hint to the optimizer to choose A before
C in the plan.

HTH,
Set

Philippe Makowski wrote:

> Hello,
>
> someone on a french newsgroup is complaining about a bad Plan
>
> Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
> join ETABLISSEMENT A on (A.NUM_ETAB = C.NUM_ETAB)
> where ((A.NUM_ETAB = 5298) ) and (C.ID_EFFECTIF <> 'T')
>
> PLAN JOIN (A INDEX (RDB$PRIMARY24),C INDEX (RDB$FOREIGN88))
>
> and :
>
> Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
> join ETABLISSEMENT A on (A.NUM_ETAB = C.NUM_ETAB)
> where ((A.NUM_ETAB = 5298) AND (A.CODE_NAF = '5299') ) and (C.ID_EFFECTIF <> 'T')
>
> PLAN JOIN (A INDEX (RDB$PRIMARY24),C INDEX (RDB$FOREIGN88))
>
> but :
>
> Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
> join ETABLISSEMENT A on (A.NUM_ETAB = C.NUM_ETAB)
> where ((A.NUM_ETAB = 5298) or (A.CODE_NAF = '5299')) and (C.ID_EFFECTIF <> 'T')
>
> PLAN JOIN (C NATURAL,A INDEX (RDB$PRIMARY24,RDB$PRIMARY24,NAF_ETABLISSEMENT_FK29))
>
> why using C NATURAL ?
> I know it is the OR that cause this, but it does not seems to be the best solution
>
> For information
> Firebird 2 give :
>
> Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
> join ETABLISSEMENT A on (A.NUM_ETAB = C.NUM_ETAB)
> where ((A.NUM_ETAB = 5298) ) and (C.ID_EFFECTIF <> 'T')
>
> PLAN JOIN (C INDEX (RDB$FOREIGN88), A INDEX (RDB$PRIMARY24))
>
> and :
>
> Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
> join ETABLISSEMENT A on (A.NUM_ETAB = C.NUM_ETAB)
> where ((A.NUM_ETAB = 5298) AND (A.CODE_NAF = '5299') ) and (C.ID_EFFECTIF <> 'T')
>
> PLAN JOIN (A INDEX (RDB$PRIMARY24), C INDEX (RDB$FOREIGN88))
>
> but :
>
> Select sum(C.EFFECTIF_CONNU) as TOTAL from DONNEES_ANNUELLES C
> join ETABLISSEMENT A on (A.NUM_ETAB = C.NUM_ETAB)
> where ((A.NUM_ETAB = 5298) or (A.CODE_NAF = '5299')) and (C.ID_EFFECTIF <> 'T')
>
> PLAN JOIN (C NATURAL, A INDEX (RDB$PRIMARY24))