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.

Answer 2

Two thoughts on this...

1. Using NULL to express the opposite of 'Y' is possibly not a good idea. Null means *I don't know what this value is' or 'there is no meaningful answer to a question'. You should really use 'N' as the opposite of 'Y'. This would eliminate the problem of searching for valid items, because Oracle will not use the index on that column when it contains only non-null values.

2. You may want to consider adding a CHECK CONSTRAINT on such a column to ensure that only legal values are entered.

Neither of these changes necessarily has any impact on DELETE performance however.

Answer 3

As Peter suggested, it's important to first verify that the index is being used for the delete. Bitmap indexes will invoke other locking for DML that could hurt overall performance.

Additional considerations:

• are there unindexed foreign key references to this table from other tables?
• are there triggers on this table that are performing other DML?

Answer 4

Drop the index on (invalid) and try both SELECT and DELETE. You already have an index on (invalid,last_validated). You should not be needing the index on invalid alone.Also approximately how many rows are there in this table ?

Answer 5

I recommend:

1. check how many records you expect the DELETE to affect (i.e. maybe there are more than you expect)
2. if the number of rows that should be deleted is relatively small, check that the index on invalid is actually being used by the DELETE
3. get a trace on the session to see what it is doing - it might be reading more blocks from disk than expected, or it might be waiting (e.g. record locking or latch contention)

Don't bother dropping or creating indexes until you have an idea of what actually is going on. You could make all kinds of changes, see an improvement (but not know why it improved), then months down the track the problem reoccurs or is even worse.