Oracle bitmapped indexes can consume far less space than a traditional b-tree Oracle index. In fact, their size can easily be computed as follows:

bitmap size = (cardinality_of_column * rows_in_table)/8

For example, suppose that the region index would have four distinct values with 800,000 rows. The entire index would only consume 100,000 bytes uncompressed--and with Oracle’s compression, this index would be far smaller than 100,000 bytes. In fact, it could probably be read entirely into the Oracle buffer with a few I/Os.

As we can see from the diagram in Figure 8.11, Oracle bitmapped indexes can dramatically reduce I/O for certain types of operations. For example, assume that we are interested in knowing the number of corporations in the western region. Because all of this information is contained entirely within the bitmapped indexes, we have no need to access the table. In other words, the query can be resolved entirely within the index.

Figure 8.11 Oracle bitmapped indexes.

How do we identify candidates for bitmapped indexes? The savvy data warehouse analyst will start by analyzing the existing database. There are two methods to use depending on whether your system is using the cost-based optimizer or the rule-based optimizer. If you are using the cost-based optimizer, then the Remote DBA_INDEXES view will contain the cardinality information that you will need to determine which index should be changed to bitmapped indexes. Listing 8.10 Can be run for cost-based optimizer systems.