At 01:25 AM 28/08/2003 +0200, you wrote:

> Hi all,
>I got a little problem with FB. Briefly, got a table with these cols:
>
>type varchar(10)
>day int
>month int
>year int
>datecol date
>and other columns...
>
>(datecol contains the same date as in day. month. year)
>
>and also two indeces:
>1) type, year, month, day
>2) type, datecol
>
>(there are about milions rows in the table and I need to prefer speed
>to disk space)
>
>And now my problem - when I use query like (FB 1, dialect 3)
>"SELECT type, x, y, z FROM tab WHERE type='sometype' AND
>datecol='1.8.2003'" I would expect, that FB will use in its plan the
>second index (type, datecol), but it always uses the first one. I
>tried to influence FB to change its plan with inserting braces and
>other conditions in where clause, but it always prefere to use index
>"type, year, month, day". I don't want to use plan definition in query
>since FB 1.5 could make problems. Please, got anyone an idea, how to
>change the query to make FB using "type, datecol" index? (This index
>is faster as I know when I specified it in plan.) Thanks.

It should be faster and it should be selected. Possible reasons why not are
1) datecol isn't a DATE type but a timestamp. In this case the optimizer
can't use this index for an equality test
2) or, if it is a DATE type, you might need to cast the date literal
explicitly.

Whether 1) or 2) applies, the following ought to force the optimizer to
choose the index you want.

SELECT type, x, y, z FROM tab WHERE type='sometype'
AND datecol=cast ('1.8.2003' as DATE)

What is the purpose of having the index on type, year, month, day anyway,
if you already have the same selection in the index that you want to
use? I would drop it.

You might also have some factors that you haven't given us. What is the
primary key? Are there any foreign keys? Optimizer confusion is a high
possibility if you have an index that steps on an integrity index.

heLen