Oracle Bitmapped Index Improvements

Oracle includes an improvement that allows better performance and storage allocation for bitmapped indexes. You typically use bitmapped indexes when there are many rows with the same value for an index's table that has a low cardinality.

A bitmapped index includes a bitmap, which indicates the presence or absence of a particular value in a particular row in a table. This bitmap is mapped back to the ROWID of the row in the table. To optimize this mapping, it helps if the bitmapped index is aware of the maximum number of rows that can exist in any particular block of a table. Having this knowledge allows a bitmapped index to allocate fewer bits for each of its own blocks, which in turn results in fewer bits per block in the bitmapped index.

In Oracle bitmap indexes, a bitmap is used to efficiently indicate the presence or absence of a particular value within specific rows of a table.
Here's a breakdown of how it works:

1. Index Creation: When a bitmap index is created on a column, Oracle generates a bitmap for each distinct value that exists within that column.
2. Bitmap Structure: Each bitmap is essentially a string of bits, where each bit in the string corresponds to a single row in the table.
3. Bit Values:
   • A bit value of 1 indicates that the row associated with that bit position contains the value represented by that particular bitmap.
   • A bit value of 0 indicates that the row does not contain the value.
4. Query Processing:
   • When a query involves filtering on the indexed column, Oracle efficiently scans the relevant bitmaps to quickly identify the rows that match the filter criteria.
   • This eliminates the need to scan through the entire table, significantly enhancing query performance.

Key Advantages of Bitmap Indexes:

1. Excellent for low-cardinality columns: Columns with a limited number of distinct values, such as gender, status flags, or region codes, are ideal candidates for bitmap indexes.
2. Superb performance for ad hoc queries: They excel in handling queries with multiple conditions using AND, OR, and NOT operators.
3. Rapid aggregations: They facilitate fast counts and sums based on indexed columns.
4. Null value support: They efficiently handle queries involving null values.

Ideal Use Cases:

• Data warehousing environments where queries often involve filtering and aggregation on low-cardinality columns.
• Read-only or infrequently updated tables, as bitmap index maintenance can be resource-intensive on heavily modified tables.

Important Considerations:

• They are not well-suited for columns with high cardinality (many distinct values), as the index size can become excessively large.
• They can have significant overhead for frequent updates or inserts, as the bitmaps need to be adjusted for each data modification.

In essence, bitmap indexes offer a powerful optimization technique for specific query patterns and data characteristics. Their efficient use can significantly boost query performance in Oracle databases, especially within data warehousing and decision support systems.**d

You use the ALTER TABLE statement to force a table to limit the number of rows in any particular block. The syntax for the feature is illustrated in the following Diagram.

The command "NOMINIZE/MINIMIZE" no longer used in Oracle as of today, January 1, 2024.
It was used in earlier versions of Oracle to control the physical storage of table rows, but it has been deprecated and is not supported in current Oracle Database releases.
Here's a breakdown of why and when it was removed:

• Purpose: NOMINIZE was used to specify the number of rows to be stored in each data block of a table. This could potentially improve performance for certain access patterns, but it also added complexity to database management.
• Removal: Oracle started phasing out NOMINIZE in Oracle8i and completely removed it in Oracle9i.
• Replacement: Oracle now automatically manages row storage using more sophisticated algorithms, making manual control through NOMINIZE unnecessary.

If you encounter code using NOMINIZE:

• Update: If possible, rewrite the code to use current Oracle features.
• Older Versions: If you're working with older Oracle versions (pre-9i), consult the documentation for specific guidance on NOMINIZE usage and its implications.

Key takeaways:

• NOMINIZE is not a valid command in current Oracle Database versions.
• Oracle now handles row storage optimization automatically.
• Adapt code using NOMINIZE to align with current Oracle practices.

Many Oracle systems store rows as small and then expand them with SQL UPDATE statements causing the rows to fragment into multiple blocks. To prevent the inevitable row migration that results from extreme row size expansion, I set PCTFREE to a high value whenever rows are stored small, and later expanded.