This topic describes bitmap indexes and their applicable scenarios.
Background information
Bitmap indexes are a special type of index used in Oracle databases. This type of index helps increase the query speed for low-cardinality fields. Due to large lock granularity, bitmap indexes are not suitable for frequently updated fields.
In PolarDB for Oracle databases, you can create B-tree indexes on low-cardinality fields to improve the query speed. In addition, B-tree indexes prevent database performance deterioration caused by large lock granularity.
Solution
You can evaluate the cardinality of a field based on the number of distinct field values. If a field has a large number of distinct values, the field has a high cardinality. If your business requires SQL query statements related to the field, we recommend that you create a B-tree index on the field. If a field has a small number of distinct values, the field has a low cardinality. We recommend that you do not create an index on the field.
Examples
- Example 1
The name column of Table A in an Oracle database has a high cardinality, and a bitmap index is created on the name column. After the table is migrated to a PolarDB for Oracle database, you can create a B-tree index on the name column.
- Example 2
The status column of Table A has a low cardinality. 90% of the column values are 1 and 10% are 0. If you need to query data records whose value in the status column is 1, we recommend that you do not create an index on the status column. If you need to query data records whose value in the status column is 0, we recommend that you create a B-tree index on the status column.