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))