Originally posted by tamilselvan
I would go for bitmap index.

The reasons:

1 Since the table gets 100 rows only every month, it tells that it is a DSS/DW system, definitely not a OLTP system.
2 The CREDIT_RATING column takes only 10 values. Assume they are all distributed evenly, then 'BAD' and 'F' may have 1 Million rows. And you are going to read 20 % of the rows from the table.
3 The column credit_rating is not "updated frequently". So no issue of locking.
4 The bitmap index is going to consume less space than the b-tree index.
5 Obviously the where clause will be like "CREDIT_RATINGS = 'BAD' or CREDIT_RATINGS = 'F'. Bitmap index works very well with LOGICAL OR conditions compared to b-tree index. You have to add combined_index hint the query. For this reason alone, I would like to opt for bitmap index.

Tamil
Well, I'm going to have to go ahead and disagree with you there.

1. It tells you that it's an academic question not a real life one. 5 million rows, but only 100 new rows per month? Gimme a break.

2. Rows-shmows -- it's blocks that count. If the rows are distributed evenly then that's probably 100% block retrieval, so a full-scan would be faster.

3. Update it once per year per row, which would meet a definition of infrequent, and you've still got 5 million changes per year. Assume they only change during working hours and that gives you 40 changes per minute.

4. Yes, but that's not the primary concern, just a nice feature.

5. Moot point, since the index would probably never be used, but b-tree's aren't that inefficient.