Why would Oracle ignore a "perfect" index?

Question

I have this table:

create table demo (
    key number(10) not null,
    type varchar2(3) not null,
    state varchar2(16) not null,
    ... lots more columns ...
)

and this index:

create index demo_x04 on demo(key, type, state);

When I run this query

select * from demo where key = 1 and type = '003' and state = 'NEW'

EXPLAIN PLAN shows that it does a full table scan. So I dropped the index and created it again. EXPLAIN PLAN still says full table scan. How can that be?

Some background: This is historical data, so what happens is that I look up a row with state CLEARED and insert a new row with state NEW (plus I copy a few values from the old row). The old row is then updated to USED. So the table always grows. What I did notice is that the cardinality of the index is 0 (despite the fact that I have thousands of different values). After recreating, the cardinality grew but the CBO didn't like the index any better.

The next morning, Oracle suddenly liked the index (probably slept over it) and started to use it but not for long. After a while, the processing dropped from 50 rows/s to 3 rows/s and I saw again "FULL TABLE SCAN". What is going on?

In my case, I need to process about a million rows. I commit the changes in batches of ca. 50. Is there some command which I should run after a commit to update/reorg the index or something like that?

I'm on Oracle 10g.

[EDIT] I have 969'491 distinct keys in this table, 3 types and 3 states.

Answer 1

What happens if you specify an index hint? Try this:

SELECT /*+ INDEX (demo demo_x04) */ * 
  FROM demo 
 WHERE key = 1 
   AND type = '003' 
   AND state = 'NEW';

It sounds like what happened overnight was that the table got analyzed. Then, as you ran your processing against the table, enough of the index was updated to cause oracle's table's statistics to go stale again and the optimizer stopped using the index.

Add the hint and see if EXPLAIN PLAN gives you a different plan and the query performs better.

Oh, and Tony's answer regarding analyzing the table is a general good practice, although with 10g the database is pretty good about doing self-maintenance in that regard. If your process is doing a lot of updates the index can go stale quickly. If running analyze when your process starts going in the ditch improves the situation for a while, you would then know this is the problem.

To update statistics for the table, use the dmbs_stats.gather_table_stats package.

For example:

exec dbms_stats.gather_table_stats('the owner','DEMO');