Index problem

I have a problem with an index which has been puzzling me. The index is on a single integer-valued column, non-unique. After a considerable amount of insert/delete activity, a state is reached where what should be fast queries are no longer so, in particular:

    SELECT MIN(COL) FROM TABLE; runs slowly (COL = indexed column), whereas

    SELECT MAX(COL) FROM TABLE; runs very quickly, as expected. Query plans for both queries indicate identical plans, i.e. using a scan on the relevant index rather than the table, but only the SELECT MAX() works properly. Evidently, something strange happens to the B-tree which makes it difficult to locate the low key, but not the high key. Rebuilding the index with ALTER INDEX REBUILD UNRECOVERABLE fixes the problem, i.e. SELECT MIN() is fast again.

Can anyone offer an explanation of what is causing this, and how to avoid it?

Thanks.

• Greg Johnson