Hi.



I have this statement



select

count(*), o.Name, rt.description

from

Log l

Join Patient p on (l.urn = p.log_urn)

left outer Join Result_types rt on (p.cons_type = rt.code)

left outer Join Organisation o on (p.surgery = o.code)

Where

(l.taken_at >= '01 Apr 2005 00:00:00' and l.Taken_at <= '05 apr 2005
00:00:00')

and

p.surgery in ('MAY','GREEN','HEATH','HILLV','PIR','STJOH', 'SHEER',
'SOUTH', 'PARIS', 'SUNNY', 'YORK1', 'COLL', 'WEST1', 'WEST2', 'CHOB')

Group By

o.Name, rt.description



and the plan comes out as follows;

PLAN SORT (JOIN (JOIN (JOIN (P NATURAL,L INDEX (RDB$PRIMARY28)),RT INDEX
(RDB$PRIMARY48)),O INDEX (RDB$PRIMARY34)))



This query takes about 21 seconds to return;





If I change the joins to be just normal inner joins (which I do not
really want as I need to also see record counts for blank cons_types
values) as below it;



select

count(*), o.Name, rt.description

from

Log l

Join Patient p on (l.urn = p.log_urn)

Join Result_types rt on (p.cons_type = rt.code)

Join Organisation o on (p.surgery = o.code)

Where

(l.taken_at >= '01 Apr 2005 00:00:00' and l.Taken_at <= '05 apr 2005
00:00:00')

and

p.surgery in ('MAY','GREEN','HEATH','HILLV','PIR','STJOH', 'SHEER',
'SOUTH', 'PARIS', 'SUNNY', 'YORK1', 'COLL', 'WEST1', 'WEST2', 'CHOB')

Group By

o.Name, rt.description



the plan comes out as followsl

PLAN SORT (JOIN (L INDEX (IX_LOG_TAKEN),P INDEX (RDB$PRIMARY37),RT INDEX
(RDB$PRIMARY48),O INDEX (RDB$PRIMARY34)))



You will notice the optomiser has used the IX_LOG_TAKEN index here which
is very quick, and is what I want it to use in the first example, can
someone tell me why fb is using completely different plans and what I
would consider ignoring the fastest index.



Thanks for any help.



Martin



[Non-text portions of this message have been removed]