Why does performance degrade when many UPDATE, INSERT, or DELETEstatements are issued on a table that has an associated bitmap index?

Why does performance degrade when many UPDATE, INSERT, or DELETEstatements are
issued on a table that has an associated bitmap index?

Why does performance degrade when many UPDATE, INSERT, or DELETEstatements are
issued on a table that has an associated bitmap index?

A.
The DML operations re-create the bitmap index blocks.

B.
The bitmap index is rebuilt automatically after a DML operation.

C.
The smallest amount of a bitmap that can be locked is a bitmap segment.

D.
Additional time is taken to remove NULL values from the bitmap index alter a DML operation.

Show Hint

Leave a Reply 3

Your email address will not be published. Required fields are marked *

rose

rose

can some one explain why c is the correct answer?

thanks

Reply

Prateek

Prateek

Answer : C

Explanation :
http://docs.oracle.com/cd/B10500_01/server.920/a96533/data_acc.htm#8131

DML and DDL statements, such as UPDATE, DELETE, and DROP TABLE, affect bitmap indexes the same way they do traditional indexes; the consistency model is the same. A compressed bitmap for a key value is made up of one or more bitmap segments, each of which is at most half a block in size (although it can be smaller). The locking granularity is one such bitmap segment. This can affect performance in environments where many transactions make simultaneous updates. If numerous DML operations have caused increased index size and decreasing performance for queries, then you can use the ALTER INDEX … REBUILD statement to compact the index and restore efficient performance.

A B-tree index entry contains a single rowid. Therefore, when the index entry is locked, a single row is locked. With bitmap indexes, an entry can potentially contain a range of rowids. When a bitmap index entry is locked, the entire range of rowids is locked. The number of rowids in this range affects concurrency. As the number of rowids increases in a bitmap segment, concurrency decreases.

Locking issues affect DML operations and can affect heavy OLTP environments. Locking issues do not, however, affect query performance. As with other types of indexes, updating bitmap indexes is a costly operation. Nonetheless, for bulk inserts and updates where many rows are inserted or many updates are made in a single statement, performance with bitmap indexes can be better than with regular B-tree indexes.

Reply