Any idea why the optimizer (on both FB 1.5.3 and 2.0rc1) chooses a
natural plan and ignores the index on PBM_CLAIM.DATESBM?
The duplicates in the index shouldn't be a problem, there number of
transactions a day vary from less than a hundred to just over a
thousand.

select
p.ACCOUNTID, p.SRVPROVID, p.PRODUCTID, d.GPI
from
pbm_claim p
JOIN MDDB_DRUG d
ON d.PRODUCTID = p.PRODUCTID
where
p.datesbm < ?

PLAN SORT (JOIN (D NATURAL, P INDEX (I_PBM_CLAIM_NDC)))

ALTER TABLE MDDB_DRUG ADD CONSTRAINT PK_MDDB_DRUG PRIMARY KEY
(PRODUCTID);
CREATE DESC INDEX I_PBM_CLAIM_DATESBM_DESC ON PBM_CLAIM (DATESBM);
CREATE ASC INDEX I_PBMCLAIM_DATESBM ON PBM_CLAIM (DATESBM);
CREATE ASC INDEX I_PBM_CLAIM_NDC ON PBM_CLAIM (PRODUCTID);
CREATE ASC INDEX I_PBMCLAIM_DECIMALQTY ON PBM_CLAIM (DECIMALQTY);

Index I_PBMCLAIM_DATESBM (1)
Depth: 2, leaf buckets: 309, nodes: 514876
Average data length: 0.00, total dup: 514037, max dup: 1233
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 308