Index on column with only 2 distinct values

Question

I am wondering about the performance of this index:

I have an "Invalid" varchar(1) column that has 2 values: NULL or 'Y' I have an index on (invalid), as well as (invalid, last_validated) Last_validated is a datetime (this is used for a unrelated SELECT query)

I am flagging a small amount of items (1-5%) of rows in the table with this as 'to be deleted'.
This is so when i

 DELETE FROM items WHERE invalid='Y'

it does not perform a full table scan for the invalid items.

A problem seems to be, the actual DELETE is quite slow now, possibly because all the indexes are being removed as they are deleted.

Would a bitmap index provide better performance for this? or perhaps no index at all?

Answer 1

Index should be used, but DELETE can still take some time.

Have a look at the execution plan of the DELETE:

EXPLAIN PLAN FOR
  DELETE FROM items WHERE invalid='Y';

SELECT * FROM TABLE( dbms_xplan.display );

You could try using a Bitmap Index, but I doubt that it will have much impact on performance.

---

Using NULL as value is not a good idea. The query

SELECT something FROM items WHERE invalid IS NULL

would not be able to use your index, since it only contains not-null values.