Index--Bitmap index

Bitmap indexes are well suited for decision support systems (decision supports SYSTEM,DSS) and data warehouses, and they should not be used for tables accessed through transactional applications. They can access very large tables using a column that is less than the medium cardinality (the number of different values). Although the bitmap index can be up to 30 columns, they are usually used only for a small number of columns.

For example, your table might contain a column called Sex, which has two possible values: male and female. This cardinality is only 2 if the user frequently queries the table based on the value of the sex column, which is the base column of the bitmap index. When more than one bitmap index is contained within a table, you can appreciate the true power of the index of the map. If there are multiple bitmap indexes available, Oracle can combine result sets from each bitmap index to quickly delete unnecessary data.

The following list of programs gives an example of creating a bitmap index:

Create bitmap index DEPT_IDX2_BM on dept (DEPTNO); index created.

Skills:

Bitmap indexing is required for columns with lower cardinality. The sex column is an example of two possible values: male or female (base is only 2). Bitmaps are very fast for low cardinality (a small number of different values), because the size of the index is much smaller relative to the B-tree index. Because these indexes are low-cardinality B-tree indexes, they are very small, so you can frequently retrieve more than half of the rows in a table, and still use bitmap indexes.

When most entries do not add a new value to the bitmap, the bitmap index is usually better than B-tree to load the table (insert operation) in a batch (single user) operation. The bitmap index should not be used when multiple sessions are inserting rows into a table at the same time, which can occur in most transactional applications.

Bitmap Index Example

Here's a sample table participant, which contains survey data from individuals. The columns Age_code, Income_level, Education_level, and marital_status all include their respective bitmap indexes. Figure 2-4 shows the data balance in each histogram and the execution path to the query that accesses each bitmap index. The execution path in the diagram shows how many single-bit graph indexes have been merged, and you can see that performance has been significantly improved.

(Click to view larger image) Figure 2-4 Bitmap index creation process

As shown in 2-4, the optimizer uses 4 separate bitmap indexes, which are referenced in the WHERE clause. Each bitmap record pointer, such as 0 or 1, is used to indicate which rows in the table contain the known values in the bitmap. With this information, Oracle performs a bitmap and operation to find which rows will be returned from all 4 bitmaps. The value is then converted to the ROWID value, and the query continues to complete the remaining processing work. Note that all 4 columns have a very low cardinality, and the index can be used to return a matching row very quickly.

Skills:

When you combine multiple bitmap indexes in a single query, you can make performance improvements significantly. Bitmap indexes use fixed-length data types that are better than variable-length data types. Larger blocks also improve the storage and reading performance of bitmap indexes.

The following query can display the index type. The B-Tree index is listed as normal, whereas the bitmap index has a type value of bitmap.

Select Index_name, Index_typefrom user_indexes;

Skills:

If you want to query the bitmap index list, you can query the Index_type column in the user _indexes view.

We recommend that you do not use bitmap indexing in some online transaction processing (OLTP) applications. The index value of the B-tree index contains ROWID so that Oracle can lock the index at the row level. The bitmap index is stored as a compressed index value, which contains a range of ROWID, so Oracle must lock all ranges of rowid for a given value. This type of locking may cause a deadlock in some DML statements. The SELECT statement is not affected by this locking issue.

There are many limitations to bitmap indexing, as follows:

The rule-based optimizer does not consider bitmap indexing.

When you execute an ALTER TABLE statement and modify a column that contains a bitmap index, the bitmap index is invalidated.

The bitmap index does not contain any column data and cannot be used for any type of integrity check.

Bitmap indexes cannot be declared as unique indexes.

The maximum length of a bitmap index is 30.

Skills:

Do not use bitmap indexing in heavy OLTP environments

Index--Bitmap index