Background information

A Bitmap index is a special type of index used in Oracle. It is suitable for low-cardinality fields and increases the query speed. Due to the large lock granularity, it is not suitable for frequently updated fields.

For low-cardinality field in PolarDB-O also can create a Btree index, create a Btree index can not only improve the search speed and does not occur due to the Oracle considerable line performance degradation of the problem.

New solution

Evaluate whether a field is low-cardinality based on the field data. If a query statement with good selectivity and this condition in the business SQL statement is used, we recommend that you create a B- tree index. If the selectivity is not good, first evaluate whether an index needs to be created.

Examples

  • Example 1

    The name column in Table A has good selectivity. A Bitmap index is available in Oracle. The B- tree index can be created after data is migrated to PolarDB-O.

  • Example 2

    The status column in Table A is poorly selective, for example, the value of 90% is 1 and the value of 10% is 0. In all business queries, the condition is status=1. In this case, you can not create an index. If status=0 query exists in your business, we recommend that you create a B- tree index.