Bitmap indexes
Bitmap indexes are not recommended for OLTP environments. There are concurrency issues when
bitmap indexes are built on tables incurring DML. Bitmap indexes are recommended for read-only
applications, and read mostly application such as OLAP.


Use indexes to improve SQL performance.
• Use B*Tree indexes:
– For join columns
– For columns frequently appearing in WHERE clauses
• Use composite indexes:
– For columns frequently used together in WHERE clauses
– Consider using compressed key indexes for large indexes
• Use bitmap indexes:
– For columns with few distinct values and large number of
rows
– For read-only or read-mostly tables
– For WHERE clause with multiple predicates

B*Tree indexes on the columns most used in the WHERE clause. Indexing a column frequently used
in a join column can improve join performance. Do not use B*Tree indexes on columns with only a
few distinct values, bitmap indexes are a better choice for these.
Composite B*Tree indexes: Consider composite indexes over columns that frequently appear
together in the same WHERE clause. Composite indexes of several low selectivity columns may
have a higher selectivity. Place the most frequently used column first in the index order. For large
composite indexes, consider using a compressed index. Index key compression works well on
indexes with leading columns that have a few distinct values—the leading, repeating values are
eliminated. This reduces the size of the index and number of I/Os.
Bitmap indexes can help performance where the tables have a large number of rows, the columns
have a few distinct values, and the WHERE clause contains multiple predicates using these columns.
Do not use bitmap indexes on tables or columns that are frequently updated.


Table Reorganization Methods
Online reorganization (DBMS_REDEFINITION)
• Online; data is available
• Could use considerable resources
Shrink segments
• Online; data is available
• In place, no additional space required
Export, recreate, and import
• Offline; data is not available
• Time consuming for large tables
CTAS, ALTER TABLE … MOVE
• Mostly offline; data is not available
• Out of place, requires additional space