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.
The following steps will demonstrate bitmap indexes:
Connect to SQL*Plus as user SH:
CONNECT sh@TESTDB/sh
Create a table to do some tests:
CREATE TABLE MYCUSTOMERS AS SELECT * FROM CUSTOMERS;
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...