D) Bit-mapped index
This is because both the columns GENDER and RATING has less unique values or we can say columns with less cardinality. GENDER has only 2 values (M, F) while RATING has ( 1, 2, 3, 4, 5 ). Even though these columns have less frequent values, they are queried most often.
Why Bit-mapped Index?
Let's represent the columns using bits. Rules for creating bit-map index are:
a) #bits is equal to the #rows in the table. Say table has 10k rows then we'll have 10k bits for a single bitmap index.
b) For each unique value, we have a separate index. Say for Male in GENDER we've one bit-map index and another for Female, while for rating we've 5 bit-map indices.
c) If we have any matching value in the column for the row, then that row bit will have ‘1’, else ‘0’. Below example will illustrate the fact.
Customers
CUSTID |
NAME |
GENDER |
RATING |
100 |
A |
M |
5 |
200 |
B |
F |
1 |
300 |
C |
M |
2 |
400 |
D |
F |
1 |
Bitmap indexes for Gender are:
M: 1010
F: 0101
1st bit is for 1st row and similarly thereafter.
Bitmap indexes for RATING are:
1: 0101
2: 0010
3: 0000
4: 0000
5: 1000
The query will search the bitmap index for both of this columns and perform logical ‘AND’ operation on those indexes to get the actual address of the result.
Bitmap index for M: 1010
AND
Bitmap index for 5: 1000
1000 => Implies that the first row has the result of the query.
Here fetching the bitmap index and performing the logical ‘AND’ operation to get the result is comparatively faster. Hence this method of storage is used for this kind of data.
Having said that bitmap indexes are not that useful in this scenarios:
- When there is multiple insert/update/delete on the table from different users, it may cause deadlock on the tables. It will take time to perform the DML transaction and then to update the bitmap index. Hence when there is multiple DML transaction from different users, it will not be able to perform transaction quickly and causing the deadlock.
- When there is a large number of records, there is an overhead to maintain this bitmap indexes. Each time a new record is entered, we have to modify the bitmap index throughout, which is a tedious and time consuming.