Question on Indexing

[Vipassana]

Could anypne pls answer this Q for me?

Regards,
Vipassana


The credit controller for your organization has complained that the report she runs to show customers with bad credit ratings takes too long to run. You look at the query that the report runs and determine that the report would run faster if there were an index on the CREDIT_RATING column of the CUSTOMERS table.
The CUSTOMERS table has about 5 million rows and around 100 new rows are added every month. Old records are not deleted from the table.
The CREDIT_RATING column is defined as a VARCHAR2(5) field. There are only 10 possible credit ratings and a customer's credit rating changes infrequently. Customers with bad credit ratings have a value in the CREDIT_RATINGS column of 'BAD' or 'F'.
Which type of index would be best for this column?
A. B-Tree
B. bitmap
C. reverse key
D. function-based

[Rangi]

Hi,,

Bitmap would be good for this.

[slimdave]

This is some dumb academic question, right? Coursework?

You can't just decide that bitmap isbetter without knowing how the 100 new rows a month are inserted, nor how frequently the values change. Knowing that each credit rating changes "infrequently" tells you nothing, when there's a million rows in the table.

In fact, an index might not even be beneficial in this case. What % of the rows are "BAD" or "F", and what % of blocks do they represent.?

I hate these stupid questions. Anyway, I'd guess that a function-based index might also be best. Or a b-tree.

[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. For this reason alone, I would like to opt for bitmap index.

Tamil

[slimdave]

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.

[abhaysk]

Originally posted by slimdave
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.

Well i would like to add to dave.. Even lets assume 20% rows take 20% of blocks.. then also FTS will out perform Bit map conversion to ROWIDs and Table Acces by ROWID.. !

And if done FTS with parallel option then bingo ..

Only case when i would prefer index & also usage of index should make sense is "When 1+ out of 10 values occupy to less blocks compared to rest 9- (may be of ratio 1:1000) and are frequently used"

(PS I assume dmbrc 32 or more).

Abhay.

[hrishy]

Originally posted by slimdave
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.

good answer..:-) is this the reason why you dont want to do ur
OCP ;-)dave you might even flunk :-D

regards
Hrishy

[slimdave]

Originally posted by hrishy
good answer..:-) is this the reason why you dont want to do ur
OCP ;-)dave you might even flunk :-D

regards
Hrishy

You're probably right -- born to flunk. Stupid academic crap -- you just have to double-guess what set of incorrect beliefs the questioners have.

[abhaysk]

Originally posted by slimdave
you just have to double-guess what set of incorrect beliefs the questioners have.

Most of the questioners than not think Index Scans are always good... Dave I think you would flunk for this matter