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(*) ...

Get Oracle Database 11gR2 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.