Using bitmap indexes

In the last recipe, we looked at the use of B-tree indexes in depth.

In the Oracle database, there is also another type of index available, the bitmap index, presented in this recipe.

How to do it...

The following steps will demonstrate bitmap indexes:

1. Connect to SQL*Plus as user SH:

   CONNECT sh@TESTDB/sh
   

2. Create a table to do some tests:

   CREATE TABLE MYCUSTOMERS AS SELECT * FROM CUSTOMERS;
   

3. Execute the following queries to verify the execution plan adopted by the database:

   SET AUTOT TRACE EXP STAT
   SELECT COUNT(*) FROM MYCUSTOMERS
     WHERE CUST_GENDER = ‹F›;
   SELECT COUNT(*) FROM MYCUSTOMERS
     WHERE CUST_MARITAL_STATUS = ‹single›;
   SELECT COUNT(*) FROM MYCUSTOMERS
     WHERE CUST_MARITAL_STATUS = ‹married› AND CUST_GENDER = ‹F›;
   SELECT COUNT(*) FROM MYCUSTOMERS
     WHERE CUST_MARITAL_STATUS = ‹single› AND CUST_GENDER = ‹M›
     AND CUST_YEAR_OF_BIRTH BETWEEN 1970 AND 1980;
   SELECT COUNT(*) FROM MYCUSTOMERS
     WHERE CUST_MARITAL_STATUS = ‹single›
     AND CUST_YEAR_OF_BIRTH BETWEEN 1970 AND...