Hi

I have a DB server running Firebird 2.1.1.
On this server there is 2 Databases.

Preparing this SQL:

Select
transaktioner.Afdeling_ID as AfdNr,
sum (transaktioner.salgstk) as Antal,
sum (transaktioner.Salgkr) as Spr,
sum (transaktioner.KostPr) as Kpr
from transaktioner
INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR = VARER.PLU_NR AND VARER.SERVICEYDELSE=0
where
transaktioner.art in (14,70,71) and
transaktioner.Dato>='2010-11-01 23:59:59' and
transaktioner.Pakkelinje IN (1,5)
group by
transaktioner.Afdeling_ID


in both of them returns different plans.


In 1 I get

PLAN SORT (JOIN (VARER NATURAL, TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR)))
(Selectivity: 0.000050904)



In the other I get

PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26)))
(Selectivity: 0.000236239)


The difference is the usage of RDB$PRIMARY26 on table VARER.
And it makes a hell of a lot of a difference using it or not!

The index is a VARCHAR(30) field called PLU_NR in table VARER.
The selectivity of this field is listed above.

I have just executed SET STATISTICS last night on all index.
An today several times on this index, and I can see the selectivity changes when I do.

If I recall correctly the lower the number in selectivity means the better the index is.
But above the index is choosen with a higher number and omitted with a lower number.

Is this a badly written SQL which I could change in some way to make Firebird use this index every time?

Regards
Michael