Implementing Bitmap Indexes- Tables and Constraints

Bitmap indexes are recommended for columns with a relatively low degree of distinct values (low cardinality). You should not use bitmap indexes in OLTP databases with high INSERT/UPDATE/DELETE activities, owing to locking issues; the structure of the bitmap index results in many rows potentially being locked during DML operations, which causes locking problems for high-transaction OLTP systems.

Bitmap indexes are commonly used in data warehouse environments. A typical star schema structure consists of a large fact table and many small dimension (lookup) tables.

In these scenarios, it is common to create bitmap indexes on fact table foreign key columns. The fact tables are typically inserted into on a daily basis and usually are not updated or deleted from.

Listed next is a simple example that demonstrates the creation and structure of a bitmap index. First, create a LOCATIONS table:

SQL> create table locations( location_id number,region varchar2(10));

Now, insert the following rows into the table:

SQL> insert into locations values(1,’NORTH’), (2,’EAST’), (3,’NORTH’),

(4,’WEST’), (5,’EAST’), (6,’NORTH’), (7,’NORTH’);

You use the BITMAP keyword to create a bitmap index. The next line of code creates a bitmap index on the REGION column of the LOCATIONS table:

SQL> create bitmap index locations_bmx1 on locations(region);

Bitmap indexes are effective at retrieving rows when multiple AND and OR conditions appear in the WHERE clause. For example, to perform the task find all rows with a region of EAST or WEST, a Boolean algebra OR operation is performed on the EAST and WEST bitmaps to quickly return rows 2, 4, and 5.

Bitmap indexes and bitmap join indexes are available only with the Oracle Enterprise

Edition of the database. Also, you cannot create a unique bitmap index.

Creating Bitmap Join Indexes

Bitmap join indexes store the results of a join between two tables in an index. Bitmap join indexes are beneficial because they avoid joining tables to retrieve results. The syntax for a bitmap join index differs from that of a regular bitmap index in that it contains FROM and WHERE clauses. Here is the basic syntax for creating a bitmap join index:

SQL> create bitmap index <index_name>

on <fact_table> (<dimension_table.dimension_column>) from <fact_table>, <dimension_table>

where <fact_table>.<foreign_key_column> = <dimension_table>.<primary_key_ column>;

Bitmap join indexes are appropriate in situations in which you are joining two tables, using the foreign key column (or columns) in one table relating to the primary key column (or columns) in the other table.

For example, suppose you typically retrieve the FIRST_NAME and LAST_NAME from the CUST dimension table while joining to a large F_SHIPMENTS fact table. This next example creates a bitmap join index between the F_SHIPMENTS and CUST tables:

SQL> create bitmap index f_shipments_bmx1

on f_shipments(cust.first_name, cust.last_name) from f_shipments, cust

where f_shipments.cust_id = cust.cust_id;

Now, consider a query such as this:

SQL> select c.first_name, c.last_name from f_shipments s, cust c

where s.cust_id = c.cust_id and c.first_name = ‘JIM’ and c.last_name = ‘STARK’;

The optimizer can choose to use the bitmap join index, thus avoiding the expense of having to join the tables. For small amounts of data, the optimizer will most likely choose not to use the bitmap join index, but as the data in the table grows, using the bitmap join index becomes more cost-effective than full-table scans or using other indexes.