Brief description of Oracle bitmap index _ MySQL

Oracle's Bitmap index description: Oracle indexes mainly include BTree and bitmap indexes. By default, most Btree indexes are used. this index is usually the unique index seen and clustered index. Btree is used in OLTP to speed up the query.

Bitmap indexes are quite attractive in Oracle. They are mainly used in OLAP (online data analysis), that is, data warehouse, to speed up queries, saves storage space. Generally, indexes consume a large amount of storage space. bitmap uses compression technology to reduce disk space. Btree uses a high base (that is, a column with a large data similarity), and a bitmap is used in a low base series. The basic principle of bitmap indexes is to use bitmaps instead of column values in indexes. Usually there is a very low set potential (cardinality) between the keys of a fact table and a dimension table. bitmap indexes are used to make the storage more effective. compared with B * Tree indexes, it only requires less storage space, so that more records can be read each time. In addition, compared with B * Tree indexes, bitmap indexes convert bitmap indexes into bitwise arithmetic operations for connections and aggregation, this greatly reduces the running time and greatly improves the performance.
The following considerations should be taken into account on how to reasonably use bitmap indexes in Oracle.

* If you want to use bitmap indexes, the initialization parameter STAR_TRANSFORMATION_ENABLED should be set to TRUE.

* The optimization mode should be CBO. For data warehouse environments, you should always consider using CBO (COST-BASEDOPTIMIZER ).

* Bitmap indexes should be created on the foreign key columns of each fact table. (This is just a general rule .)

In addition, the objective determination of cardinality in a data table is also a problem. The set of 10 thousand data records containing only three values is low, so it is not low to include 0.1 billion records in 30 thousand records? In this case, we recommend that you perform a few data simulation tests. generally, in the data warehouse environment, the bitmap index performance is better than the B * Tree index. Note that Bitmap indexes are not designed for the OLTP database and should not be used in large quantities in the OLTP database, especially for tables with update operations.