Database using-oracle Bitmap index

Most of the indexes we use today are generallyB*treeindex that stores key values and key values in the index structure.RowID,and is one by one corresponds. A bitmap index is primarily created for columns of the same value(Example: category, operator, DepartmentID,WarehouseIDwait),stores key values and starts and ends in an index row of an index blockRowid,and the location encoding of these key values,each of the data rows in the location code that represents the key value has no.a block might point to the location of dozens of or even hundreds of rows of data. Storing data in this way,relative toB*treeIndex,very little space to occupy,created and used very quickly.

The goal of a bitmap index is to provide the user withA pointer to adata row that contains a specific key value. In a regular index,Oracle stores the key values of each row together with a set of ROWID corresponding to this key value , thus achieving this goal. In the bitmap index (bitmapindex), you only need to store bitmaps (bitmap) for each key value, not a set of ROWID .

        Bitmap (Bitmap) in each of the bits (bit) corresponds to a possibleROWID. If a certain person is placed (Set), it indicates that the correspondingROWIDin the line that you are pointing tocontains the key value represented by this bit (Key Value). Oraclethrough a mapping function (mapping function) to translate bit information into actualROWID, so although the bitmap index (Bitmap IndexThe internal storage structure is different from the regular index, but she can also implement regular indexing functions. When the number of index keys for different values is small, the storage efficiency of the bitmap index is quite high.

if a bitmap index (bitmap index) is built on more than one column referenced withinthe WHERE clause, the bitmap index scan ( Bitmap indexing) can fuse individual bitmap indexes together. Rows that do not meet all of the criteria can be pre-filtered out. Therefore, using a bitmap index can greatly improve the response time of the query.

Advantages of bitmap indexing in data warehouse applications

Datawarehousingapplicationis characterized by a large amount of data, which is performed by custom queries (ad hoc query ), and fewer concurrent transactions. Using bitmap indexing (bitmapindex) in this environmenthas the following advantages:

· ability to reduce response time for large data volume custom queries

· Save a lot of storage space compared to other indexing technologies

· significantly improved performance even with low hardware configurations

· facilitates parallel DML and parallel loading

Establishing a traditional balanced Tree index (B-tree index) for a large tablemay occupy a significant amount of storage space, and the index may be several times larger than the data table. A bitmap index (bitmapindex) occupies a much smaller space than the indexed data.

bitmap indexing (bitmapindex) does not apply to OLTP Systems because there is a large number of concurrent transactions that modify data in such systems. The bitmap index is mainly used in the datawarehousingDecision support function, in which the user's operation to the data is mainly query rather than modification.

        The main is greater than (Greater than) or less than (Less than), it is not appropriate to use a bitmap index (Bitmap Index). For example,WHEREclause in a regularSalarycolumn is compared to a value, which is more appropriate when using the Balanced Tree index (B-tree Index). Bitmap indexes are suitable for equivalent queries, especially if there are and,OR, and notwhen combined with logical operators.

        Bitmap Index (Bitmap Index) is integrated inOraclethe optimizer (Optimizer) and the execution engine (Execution Engine). The bitmap index can also beOraclethe other execution methods inExecution Method) to seamlessly combine. For example, the optimizer can take advantage of a table's bitmap index and another table's Balanced Tree index (B-tree Index) to hash the two tables (Hash Join). The optimizer is able to index the bitmap and other available access methods (such as a regular balanced tree index, or a full table scan (Full table Scan) , choosing the most efficient way to consider whether parallel execution is appropriate.

Bitmap index (bitmapindex) can be combined with a parallel query (parallelquery) and parallel as a regular index DML(parallel DML) works together. The bitmap index established on the partition table (partitionedtable) must be a local index. Oracle also supports creating bitmap indexes in parallel, as well as creating composite bitmap indexes.

Base

         in cardinality ( cardinality A bitmap index on a small column ( bitmap index low cardinality ) refers to the number of different values in this column that are less than the total number of rows. If the percentage of all the different values in a column is less than the total number of rows in the 1%  where  in a query;

For example, if a table contains 1 million rows of data, and one of the columns contains 10,000 different values, you can consider creating a bitmap index on this column (bitmap index). The query performance for this column's upper-level index will exceed the Balanced Tree index (b-treeindex), especially when this column is combined with other columns.

the Balanced Tree index (b-treeindex) applies to high-cardinality data, which means that there are many possible values for the data, such as customer_name or c11> phone_number column. In some cases, the amount of storage space required to balance the tree index may be larger than the data being indexed. If used properly, the bitmap index will be much smaller than the balanced tree index in the same situation.

        for custom queries (ad hoc query) or similar applications, using a bitmap index (Bitmap Index) can significantly improve query performance. of the queryWHEREclause in the andand theORconditions directly on the bitmap (Bitmap) to perform a Boolean operation (Boolean Operation) to get a bitmap result set (resulting bitmap) without having to convert all bitmaps toROWID. If the result set is small after the boolean operation, the query can quickly get results without having to perform a full table scan (Full table Scan).

Bitmap indexes and Null values

Unlike most other indexes, bitmap indexes (bitmapindex) can contain key values (key value) as NULL of the line. Indexing rows with null key values is useful for some SQL statements, such as queries that contain the COUNT aggregate function.

Bitmap index on a partitioned table

        user can be in the partition table ( partitioned table bitmap index local ), not the global index ( global index                      nbsp                                 &NBSP ;                      

Bitmap Connection Index

        In addition to the bitmap index built on a single table (Bitmap Index), the user can also create a bitmap connection index (Bitmap Join Index), this type of index is for connection (Join) of two or more data tables. Bitmap Connection Index (Bitmap Join Indexyou can pre-save data that has a connection relationship and require less storage space. For each value of a column of a table, the bitmap join index holds the data rows in the other table that have a connection to this value .rowID. In a data warehouse environment, a connection relationship is typically a dimension table (dimension Table) in the primary key (PRIMARY Key) and the fact table (fact Table) in the foreign key (FOREIGN Key) for the equivalent internal connection (Equi-inner Join).

The materialized connection view (materialized JoinView) is also a pre-materialized method, but compared to the bitmap join index (bitmap Join ) requires less storage space. Because materialized connection views do not compress rowidin the fact table.

Advantages and disadvantages of bitmap indexing

olap example Report class database specific types of queries such as count , or , and

Cons: Not suitable for fields with low repetition rates, and often DML Operation ( Insert , Update , Delete because the lock cost of a bitmap index is extremely high, modifying a bitmap index segment affects the entire bitmap segment, such as modifying

A key value that affects multiple rows of the same key value, so for OLTP The system Bitmap index may not be the most suitable, even can cause serious harm to system system; BDC platform, the state of the main Entry table attempts to modify to a bitmap index, resulting in a serious blockage of inbound data.

In summary, if you want to use a bitmap index in the platform, be sure to carefully verify the performance before and after use.

There are some text and content from the Internet

Database using-oracle Bitmap index