To help tune queries that use non-selective columns in their limiting conditions, you can use bitmap indexes. Bitmap indexes should only be used if the data is infrequently updated because they add to the cost of all data-manipulation transactions against the tables they index.
NOTE
Bitmap indexes should not be used for tables involved in online transaction-processing applications because of the internal mechanisms Oracle uses to maintain them. Restrict their usage to tables involved in batch transactions.
Bitmap indexes are appropriate when non-selective columns are used as limiting conditions in a query. For example, if there are very few distinct RATING values in a very large BOOKSHELF table, you would not usually create a traditional B*-tree index on RATING, even if it is commonly used in WHERE clauses. However, RATING may be able to take advantage of a bitmap index.
Internally, a bitmap index maps the distinct values for the columns to each record. For this example, assume there are five RATING values (1, 2, 3, 4, and 5) in a very large BOOKSHELF table. Because there are five RATING values, there are five separate bitmap entries for the RATING bitmap index. If the first five rows in the table have a RATING value of 1, and the next five have a RATING value of 2, then the RATING bitmap entries would resemble those shown in the following listing:
In the preceding listing, each column of 0s and 1s represents a row in the BOOKSHELF table. Because ten rows are considered, ten bitmap values are shown. Reading the bitmap for RATING, the first five records have a RATING value of 1 (the “1” values), and the next five do not (the “0” values). There is a separate bitmap entry for each possible value.
The Oracle Optimizer can dynamically convert bitmap index entries to ROWIDs during query processing. This conversion capability allows the optimizer to use indexes on columns that have many distinct values (via B*-tree indexes) and on those that have few distinct values (via bitmap indexes).
To create a bitmap index, use the BITMAP clause of the CREATE INDEX command, as shown in the following listing. You should indicate its nature as a bitmap index within the index name so it will be easy to detect during tuning operations.
If you choose to use bitmap indexes, you need to weigh the performance benefit during queries against the performance cost during data-manipulation commands. The more bitmap indexes there are on a table, the greater the cost will be during each transaction. You should not use bitmap indexes on a column that frequently has new values added to it. Each addition of a new value to the RATING column requires that a corresponding new bitmap be created.
Leave a Reply