Re: simple query not using index

Could it be the clustering ratio? This measures the extent to which the physical ordering of table rows conforms to the order of the index keys. Ideally it is equal to the number of blocks in the table, thus Oracle only needs to read each block once. Worst case is equal to the number of rows in the table, thus Oracle must do a physical I-O for every row (minus I guess one buffer full of blocks). If it approaches the worst case, the optimizer may have concluded that the overhead of reading index blocks plus the thrashing needed to get the rows is more work than just scanning all the rows.

I'm not at work but I think you'll find the clustering ratio in DBA_INDEXES. You need to run ANALYZE to refresh it; with COMPUTE STATISTICS to refresh it accurately.

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to
deanguer@       | rely on a giraffe filled with whipped cream."
quidnunc.net    | - Frank Zappa