Database usage-oracle bitmap Index

Database usage-oracle bitmap Index

Currently, a large number of indexes are mainly B * Tree indexes. The index structure stores the rowids of key values and key values, which correspond one to one. Bitmap indexes are mainly created for a large number of Columns with the same value (for example, category, operator, department ID, and warehouse ID ), the key value and start and end Rowid are stored in an index row of the index block, as well as the location encoding of these key values. Each bit in the location encoding indicates whether the data row corresponding to the key value exists. A block may point to dozens or even hundreds of thousands of rows of data. This method is used to store data. Compared with the B * Tree index, the storage space is very small and the data is created and used very quickly.

The purpose of a bitmap index is to provide a pointer to a data row containing a specific key value. In a regular index, Oracle stores the key values of each row and a group of rowids corresponding to the key value, thus achieving the above goal. Bitmap indexes only store bitmaps of each key value, rather than a group of rowids.

Each bit in a bitmap corresponds to a possible ROWID. If a bit is set, it indicates that the row corresponding to this bit's ROWID points to contains the key value represented by this bit ). Oracle uses a ing function to convert bit information to the actual ROWID. Therefore, although the storage structure of bitmap index is different from that of conventional indexes, however, she can also implement regular indexing. When the number of index keys for different values is small, the storage efficiency of Bitmap indexes is quite high.

If bitmap indexes are built on multiple columns referenced in the WHERE clause, bitmap indexes can be combined for bitmap indexing. Rows that do not meet all the conditions can be filtered out in advance. Therefore, bitmap indexes can greatly improve the query response time.

Advantages of Bitmap Indexing in data warehouse applications

Data warehousingapplication is characterized by a large amount of data, most of which are performed by custom queries (ad hoc queries), and few concurrent transactions. Using bitmap indexes in this environment has the following advantages:

· Reduce the response time for custom queries with large data volumes

· Saves a lot of storage space compared with other indexing technologies

· Performance can be significantly improved even if the hardware configuration is low

· Benefits for parallel DML and parallel loading

Creating a traditional B-tree index for a large table may occupy a lot of storage space, and the index may be several times larger than the data table. A bitmap index occupies much smaller space than the indexed data.

Bitmap indexes are not applicable to OLTP systems, because such systems have a large number of concurrent transactions that modify data. Bitmap indexes are mainly used to support data warehousing decision-making in a data warehouse system. In such an environment, users' operations on data are mainly queries rather than modifications.

The bitmap index is not suitable for comparing larger than (greater than) or less than (less than) columns ). For example, a WHERE clause usually compares a salary column with a value. In this case, a B-tree index is more suitable ). Bitmap indexes are suitable for equivalent queries, especially when combinations of logical operators such as AND, OR, and not exist.

Bitmap indexes are integrated into Oracle optimizer and execution engine. Bitmap indexes can also be seamlessly combined with other execution methods in Oracle. For example, the optimizer can use the bitmap index of one table and the B-tree index of another table to perform hash join on the two tables ). The optimizer can select the most efficient method in Bitmap indexes and other available access methods (such as regular balance tree indexes or full table scan, at the same time, consider whether parallel execution is suitable.

Like regular indexes, bitmap indexes can work together with parallel query and parallel DML. The bitmap index created in the partition table must be a local index ). Oracle also supports parallel creation of Bitmap indexes and composite Bitmap indexes.

Base

It is best to create a bitmap index on a column with a small cardinality. Low cardinality indicates that the number of all different values in a column is smaller than the total number of rows. If the number of different values in a column accounts for less than 1% of the total number of rows, or the number of duplicates in a column is more than 100, you can create a bitmap index on this column. Even if the base of a column is slightly larger than the above standard, or the number of repeated values is slightly smaller than the above standard, if you need to reference this column to define complex conditions in a WHERE clause of a query, you can also consider creating a bitmap index on this column.

For example, if a table contains 1 million rows of data and one column contains 10 thousand different values, you can create bitmap indexes on this column ). The query performance of the bitmap index on this column will exceed that of the Balance tree index (B-tree index). It is especially effective when this column and other columns are used as combination conditions.

The B-tree index applies to data with a high base, that is, the data may have many values, such as the CUSTOMER_NAME or PHONE_NUMBER columns. In some cases, the storage space required by the balance tree index may be larger than that of the indexed data. If used properly, the bitmap index will be much smaller than the Balance Tree Index under the same circumstances.

For ad hoc queries or similar applications, bitmap indexes can significantly improve query performance. The and or conditions in the WHERE clause directly perform Boolean operation on bitmap to obtain a bitmap result set (resulting bitmap ), instead of converting all bitmaps to ROWID. If the result set after the Boolean operation is small, the query can quickly obtain the result without performing full table scan ).

Bitmap index and null value

Unlike most other indexes, bitmap indexes can contain rows with NULL key values. Indexing rows with null key values is useful for some SQL statements, such as queries containing COUNT Aggregate functions.

Bitmap indexes on partitioned tables

You can create bitmap indexes on a partition table ). The only restriction is that Bitmap indexes must be local instead of global indexes for partitioned tables ). Only non-Partitioned Tables can use global Bitmap indexes.

Bitmap join Index

In addition to bitmap indexes built on a single table, you can also create bitmap join indexes) two or more data tables. Bitmap join index (bitmap join index) can store connected data in advance, and the storage space is small. For each value of a column in a table, the bitmap connection index stores the rowid of the Data row associated with the value in other tables. In the data warehouse environment, the connection relationship is usually the primary key (primary key) in the dimension table and the foreign key (foreign key) in the fact table) perform equi-inner join ).

The materialized connection view (materialized joinview) is also a method for listing connections in advance, but it requires less storage space than bitmap join indexes. Because materialized connection views do not compress rowid in fact tables.

 

Advantages and disadvantages of Bitmap indexes

Advantages: OLAP, for example, logic operations such as count, or, and, for data with a high repetition rate in a report database, because only bit operations are required to obtain the expected results.

Disadvantages: it is not suitable for fields with low repetition rate and frequent DML operations (insert, update, delete). Because the lock cost of Bitmap indexes is extremely high, modifying a bitmap index segment affects the entire bitmap segment, for example, modify

A single key value may affect multiple rows with the same key value. Therefore, bitmap indexes of the OLTP system may not be the most suitable, or even cause serious harm to the system. (BDC platform, the status of the primary entry table is changed to a bitmap index, causing serious data congestion at that time ).

All in all, if you want to use Bitmap indexes on the platform, you must carefully verify the performance before and after use.