We have a fairly large table (96 million rows) and have always had 2 main indexes, the primary key and an index on "STATUS" field.

We recently added a new index on an ACCOUNT_NUMBER type field, and now our query that just has WHERE STATUS = ? does full table scans rather than using its index.

The only way we get it back to doing index reads is dropping the unrelated ACCOUNT_NUMBER index.

We obviously run runstats after each index creation or drop but we get these same results, the only thing that gets it back to using the index is to get rid of the 3rd index.

We can re-create this in the same table in a few different environments including 8.2 fix 14 and 9.2 fix 3, all on solaris.

Anyone else seen this?

What is the cardinality(how many distinct values) of STATUS field? If it is low(like 3 or 4) you are better off doing table scan.

Yes, I would assume this probably does have something to do with the cardinality. There are probably 10 different values of the Status field.

When we query on status we are looking for status = 1

At any given time there are from 1000 to 2 million of status 1 and the rest are other status values.

I know we are not better off doing a full scan. When just the 2 indexes are present, we use the Status index and the query runs in .5 to 2 second timeframes.

When the 3rd index is created, that has nothing to do with the Status field, it goes to full table scans and same query runs >15 minutes.

I will probably open ticket with IBM, but very strange that the presence of the unrelated index changes the access plan on the STATUS query.

You need to post your runstats command. I would recommend this:

runstats on table table-name with disitribution on key columns with detailed indexes all