Oracle Bitmap Index

by / July, 2012 / Published in Oracle DBA Tips

oracle bitmap index

Characteristics of Oracle Bitmap Indexes:

Before discussing Bitmap indexes let me explain indexes first. The index is a data structure usually used to fasten up the data retrieval operations from a table. An index is able to do so by directly pointing to the row/column of a table. We are aware that database consists of data files which in turn contain tables, in which all the data is stored. Bitmap indexes are used to manage various physical memory components in a database. Segments are also managed by bitmaps. In a locally managed segment, bitmaps are used to record free extents.

In bitmaps, a bit is used to point a block in a block. One bit can also be used to point group of blocks in a segment. Each bit in a bitmap indicates free or used. Thus the value of bit in a bitmap indicates whether the block in a segment is free or used. Bitmaps are a better way of mapping the data blocks as every time the kernel does not look for the data block, but can look for the corresponding bit in the bitmap and then can decide whether it has to write on the block or not. In this way a full segment is mapped using bits on a map and this map is called bitmap. We can also track the free and the used space of a block using bitmaps. Bitmaps are certainly a faster way to retrieve the data and to decide the status of the block in a segment. Bitmaps makes it easier to manage the space and to utilize it in a better way. It also makes it possible for two INSERT operations to be handled easily.

Oracle DBA Interview Questions

Broadly speaking, a bitmapped segment has a bitmap. A bitmap is a collection of bits. Each bit in the bitmap corresponds to the blocks in a segment (a single bit can also correspond to a group of blocks of a segment). The value of bits in a bitmap depends upon the used or empty blocks in a segment. If it is a 1 then it is used, else 0 indicates a free block (group of blocks) in a segment. When we try to insert a new row the server searches the map for a block with sufficient space. It looks for all the bits with value 0. The location of the bitmap for a segment is contained in a separate set of blocks and is referred to as bitmapped blocks (BMBs).

Bitmap is available in from Oracle 7.3. Earlier versions of Oracle database were managed by several lists. Bitmap indexes also are very helpful in data warehousing. Bitmap indexes are also useful in linking large tables. Bitmap provides faster and more reliable data retrieval. It manages the free space efficiently and thus is able to organize the segment in a better fashion.

We have been getting several calls regarding Oracle trainings but unfortunately we stopped training department due to heavy work of remote dba support work. At present we are offering only remote database support and database services for Oracle database. We have long list of happy clients due to maintaining strong SLA and response time matrix in Oracle support. Our remote dba support and dba specialists are having large experience to manage critical large Oracle databases in complex environment. We offer 24/7 database monitoring with strong SLA and response time.
