> This table and index returns the these plans for the given querys. I'd
> like to involve RXCLAIMNBR and CLAIMSTS in the lookup, but CLAIMSTS is
> the third item in the key and I don't care about the second. Also, the
> bitmap created by the I_SXC_CLAIM_DATESBM index will probably be huge,
> and it won't really filter the results returned by PK_SXC_CLAIM which
> are have a maximum of 43 duplicates on the first portion of the key, and
> normally run a few at most.
>
> From the plans I can tell that it's using the primary key, but I don't
> know if it is ever using the third part of the key. I can tell that if
> I use any query other than the simple one, I'll have to make sure I
> modify it to exclude the index on date. FB2 is smart enough to not use
> the date index.

A compound index is only useful if you use the first N fields without
skipping any. Given that Firebird can combine indices on multiple
fields into a single bitmap (unlike most other DBMS), I wonder if
there is any reason (other than disk space) that one should ever use
compound indices. In any case, I would avoid the candidate key you (or
someone else) selected as the PK and create a surrogate field. You can
create a unique constraint across these fields if the business rules
desire this. Then create indices on the fields seperately if you use
random combinations of these fields in queries.

>
> RECREATE TABLE SXC_CLAIM
> (
> RXCLAIMNBR D_SXC_CLAIMNBR NOT NULL,
> CLMSEQNBR D_SXC_CLAIMSEQ NOT NULL,
> CLAIMSTS D_SXC_CLAIMSTS NOT NULL
> CONSTRAINT PK_SXC_CLAIM PRIMARY KEY (RXCLAIMNBR, CLMSEQNBR, CLAIMSTS)
> );
> CREATE ASC INDEX I_SXC_CLAIM_DATESBM ON SXC_CLAIM (DATESBM);
>
> /* simple query */
> Query 1:
> SELECT 1
> FROM SXC_CLAIM sc
> WHERE sc.RXCLAIMNBR = ?
> AND sc.CLAIMSTS IN ('P','X')
> AND sc.DATESBM >= ?
>
> Plan 1:
> PLAN (SC INDEX (PK_SXC_CLAIM))

It uses the PK index to evaluate 'WHERE sc.RXCLAIMNBR = ?', better
than nothing I suppose.

>
> /* break constraints out to try to improve plan */
> Query 2:
> SELECT 1
> FROM SXC_CLAIM sc
> WHERE
> ((sc.RXCLAIMNBR = ? AND sc.CLAIMSTS = 'P') OR
> (sc.RXCLAIMNBR = ? AND sc.CLAIMSTS = 'X')) AND
> sc.DATESBM >= ?
>
> Plan 2:
> PLAN (SC INDEX (I_SXC_CLAIM_DATESBM,PK_SXC_CLAIM,PK_SXC_CLAIM))
>
> /* CLMSEQNBR is always greater than zero, but try to involve CLAIMSTS in
> lookup */
> Query 3:
> SELECT 1
> FROM SXC_CLAIM sc
> WHERE
> ((sc.RXCLAIMNBR = ? AND sc.CLMSEQNBR > 0 AND sc.CLAIMSTS = 'P') OR
> (sc.RXCLAIMNBR = ? AND sc.CLMSEQNBR > 0 AND sc.CLAIMSTS = 'X')) AND
> sc.DATESBM >= ?
>
> Plan 3:
> PLAN (SC INDEX (I_SXC_CLAIM_DATESBM,PK_SXC_CLAIM,PK_SXC_CLAIM))
>

Not sure what you are trying with 2 and 3. They look like the same
plan to me, which means FB was smart enough to effectively convert one
to the other.

If you want to avoid the index on the datesbm, add 0 like below:

sc.DATESBM+0 >= ?

Adam