Hi,

> 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

No, datecol is definitely date type.

> 2) or, if it is a DATE type, you might need to cast the date literal
> explicitly.

I do.

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

I three cols year\month\day are better when I need to select data for
one month or mainly group by month or day. And date type column is
necessary when I need data in some range (eg. 15.8.2003 - 14.8.2003).

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

Here's an extract from tables DDL. I've done some notes to it for you:

CREATE TABLE MASTER (
ID INTEGER NOT NULL, /* Primary key */
PTYP VARCHAR (10) character set WIN1250 collate PXW_CSY,
IP VARCHAR (15) character set WIN1250 collate PXW_CSY,
DEN INTEGER, /* day */
MESIC INTEGER, /* month */
ROK INTEGER, /* year */
DATUM DATE, /* see, NOT timestamp but date */
... and other columns we don't need to care about now )

ALTER TABLE MASTER ADD CONSTRAINT PK_MASTER_ID PRIMARY KEY (ID);

CREATE INDEX MASTER_PTYPDATUM ON MASTER (PTYP, DATUM);
/* means INDEX MASTER_PTYPDATE (PTYP, DATE) */
CREATE INDEX MASTER_PTYPROKMESIC ON MASTER (PTYP, ROK, MESIC);
/* and MASTER_PTYPYEARMONTH (PTYP, YEAR, MONTH) */

... and some others not important now...

There are no other foreign/unique keys.
Date column is filled in trigger:
NEW.DATUM = cast (NEW.den || '.' || NEW.mesic || '.' || NEW.rok as date);

Now, when I've got query such as this:
SELECT COUNT(*) FROM master WHERE ptyp='sometype' AND datum='1.8.2003'
I would expect that index countaining "ptyp, date" is going to be
used. But this plan is used (for the query above):

Plan:
PLAN (MASTER INDEX (MASTER_PTYPROKMESIC))
Adapted plan:
PLAN (MASTER INDEX (MASTER_PTYPROKMESIC))

It is index "ptyp, year, month". Why? I tried recomputing of both
indices or changing the query - all in vain.

When I define plan (in the case above):

SELECT COUNT(*) FROM master WHERE ptyp='sometype' AND datum='1.8.2003'
PLAN (MASTER INDEX (MASTER_PTYPDATUM))

FB uses the index correctly without any error message. But why don't
optimizer chooses it naturally? Even in complicated queries, when
optimizer decides to use index "ptyp, date" (and I don't use year or
month in the query, just date), it is used ALWAYS after the index
"ptyp, year, month".

So, does anybody please know solution of this problem? Hope these
information are sufficient.

Thanks...

> heLen
>>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.

--
Martin (mailto:moirae@...)