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.

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

/* 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))