The applicable conditions for Oracle-bitmap indexing

applicable conditions for bitmap indexing

Bitmap indexes are suitable for columns with only a few fixed values, such as gender, marital status, administrative district, and so on, while the type of the ID number is not suitable for bitmap indexing.

Bitmap indexes are suitable for static data, not for columns that are frequently updated by indexes.

For example, there is a field busy, recording the busy of each machine or not, when the machine is busy, busy is 1, when the machine is not busy, busy is 0.

This time someone would say to use a bitmap index because busy has only two values. OK, we use the bitmap index to index the busy field! Suppose user A updates the busy value of a machine with update, such as the Update table set Table.busy=1 where rowid=100, but there is no commit, and User B updates the busy value of the other machine with update, Update table set Table.busy=1 where rowid=12; This time User B can not update, need to wait for user a commit.

Cause: User A updated the busy value of a machine to 1, causing all the busy of the machine's bitmap vectors to change, so that the database locks all the rows of the Busy=1 and is unlocked only after a commit.

Question: Why is updating a machine with a busy value of 1, which causes all busy of the machine to change the bitmap vectors, so that the database locks all busy=1 rows?

See the storage structure of the bitmap index for the answer.

The storage of bitmaps, the number of bits in the bitmap, and the number of records in the table items,

For example, the following marital status table

Names (name)	Sex (Gender)	Marital status (marital)
Tom	Man	Married
John doe	Woman	Married
Harry	Man	Unmarried
Zhao Liu	Woman	Divorce
Sun Seven	Woman	Unmarried

There are 5 rows of records in the table, so the records created will have 5-bit

Sex Column-Men's bitmap: 10100

Sex Columns-Women's bitmaps: 01011

Bitmap for married column: 11000

Bitmap for unmarried column: 11000

Bitmap of the Divorce column: 11000

So now you know, when you want to update the bitmap index, the bitmap will be updated, then the entire bitmap or the block of the bit to be updated is locked.

The applicable conditions for Oracle-bitmap indexing