Bitmap Indexes

位圖索引的使用：

1.Bitmap indexing benefits data warehousing applications

2.Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data.

3.Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons.

4.Bitmapped indexes are only useful with equality queries, especially in combination with AND, OR, and NOT operators.

5.The advantages of using bitmap indexes are greatest for low cardinality columns

6.B-tree indexes are most effective for high-cardinality data

7.In ad hoc queries and similar situations, bitmap indexes can dramatically improve query performance. AND and OR conditions in the WHERE clause of a query can be quickly resolved by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to rowids.

8.bitmap indexes must be local to the partitioned table

9.Bitmap indexes can include rows that have NULL values

The

purpose of an index

is to

provide pointers to the rows in a table

that contain a given key value. In a

regular index

, this is achieved by

storing a list of rowids for each key

corresponding to the rows with that key value.

Oracle stores each key value repeatedly with each stored rowid

. In a

bitmap index

, a bitmap for each key value is used instead of a list of rowids.（

索引的目的是：提供指向表中的行的指針，在正常索引中，為每個健值存儲一個 rowid 清單，即一個健值對應一個 rowid 。 在位圖索引中，每個健值對應一個位圖） Each bit

in the bitmap

corresponds to a possible rowid

. If the

bit is set

, then it means that

the row with the corresponding rowid contains the key value

. A

mapping function converts

the

bit position to

an actual

rowid,

so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally.

If the number of different key values is small, then bitmap indexes are very space efficient. （ 如果不同鍵值的數目即鍵值的選擇性較小，那麼使用位圖索引可以節省空間）

Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.

Benefits for Data Warehousing Applications Bitmap indexing benefits data warehousing applications

which have

large amounts of data

and

ad hoc queries

but a low level of concurrent transactions. For such applications, bitmap indexing provides:

• Reduced response time for large classes of ad hoc queries
• A substantial reduction of space use compared to other indexing techniques
• Dramatic performance gains even on very low end hardware
• Very efficient parallel DML and loads

Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space, because the index can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data

. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.

Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons.

For example, a salary column that usually appears in WHERE clauses in a comparison to a certain value is better served with a B-tree index.

Bitmapped indexes are only useful with equality queries, especially in combination with AND , OR , and NOT operators. Bitmap indexes are integrated with the Oracle optimizer and execution engine

. They can be used seamlessly in combination with other Oracle execution methods. For example, the optimizer can decide to perform a hash join between two tables using a bitmap index on one table and a regular B-tree index on the other. The optimizer considers bitmap indexes and other available access methods, such as regular B-tree indexes and full table scan, and chooses the most efficient method, taking parallelism into account where appropriate.

Parallel query and parallel DML work with bitmap indexes as with traditional indexes. Bitmap indexes on partitioned tables must be local indexes. Parallel create index and concatenated indexes are also supported. Cardinality

The advantages of using

bitmap indexes

are

greatest for low cardinality columns

: that is, columns in which the number of distinct values is small compared to the number of rows in the table. I

f the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.

Even columns with a lower number of repetitions and thus higher cardinality can be candidates if they tend to be involved in

complex conditions in the WHERE clauses of queries.

For example, on a table with 1 million rows, a column with 10,000 distinct values is a candidate for a bitmap index. A bitmap index on this column can out-perform a B-tree index, particularly when this column is often queried in conjunction with other columns.

B-tree indexes are most effective for high-cardinality data

: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER. In some situations, a B-tree index can be larger than the indexed data. Used appropriately, bitmap indexes can be significantly smaller than a corresponding B-tree index.

In ad hoc queries and similar situations, bitmap indexes can dramatically improve query performance. AND and OR conditions in the WHERE clause of a query can be quickly resolved by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to rowids. If the resulting number of rows is small, the query can be answered very quickly without resorting to a full table scan of the table. Bitmap Index Example

Table 5-1 shows a portion of a company's customer data.

Table 5-1 Bitmap Index Example

CUSTOMER #	MARITAL_ STATUS	REGION	GENDER	INCOME_ LEVEL
101	single	east	male	bracket_1
102	married	central	female	bracket_4
103	married	west	female	bracket_2
104	divorced	west	male	bracket_4
105	single	central	female	bracket_2
106	married	central	female	bracket_3

MARITAL_STATUS, REGION, GENDER, and INCOME_LEVEL are all low-cardinality columns. There are only three possible values for marital status and region, two possible values for gender, and four for income level. Therefore, it is appropriate to create bitmap indexes on these columns. A bitmap index should not be created on CUSTOMER# because this is a high-cardinality column. Instead, use a unique B-tree index on this column to provide the most efficient representation and retrieval.

Table 5-2 illustrates the bitmap index for the REGION column in this example. It consists of three separate bitmaps, one for each region.

Table 5-2 Sample Bitmap

REGION='east'	REGION='central'	REGION='west'
1
1
1
1
1
1

Each entry or bit in the bitmap corresponds to a single row of the CUSTOMER table. The value of each bit depends upon the values of the corresponding row in the table. For instance, the bitmap REGION='east' contains a one as its first bit. This is because the region is east in the first row of the CUSTOMER table. The bitmap REGION='east' has a zero for its other bits because none of the other rows of the table contain east as their value for REGION.

An analyst investigating demographic trends of the company's customers can ask, "How many of our married customers live in the central or west regions?" This corresponds to the following SQL query:

SELECT COUNT(*) FROM CUSTOMER

WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');

Bitmap indexes can process this query with great efficiency by counting the number of ones in the resulting bitmap, as illustrated in Figure 5-8. To identify the specific customers who satisfy the criteria, the resulting bitmap can be used to access the table.

Figure 5-8 Running a Query Using Bitmap Indexes Bitmap Indexes and Nulls Bitmap indexes can include rows that have NULL values

, unlike most other types of indexes.

Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function COUNT . Bitmap Indexes on Partitioned Tables

Like other indexes, you can create bitmap indexes on partitioned tables. The only restriction is that

bitmap indexes must be local to the partitioned table

—they cannot be global indexes. Global bitmap indexes are supported only on nonpartitioned tables.

Bitmap Join Indexes

In addition to a bitmap index on a single table, you can create a bitmap join index, which is a bitmap index for the join of two or more tables. A bitmap join index is a space efficient way of reducing the volume of data that must be joined by performing restrictions in advance. For each value in a column of a table, a bitmap join index stores the rowids of corresponding rows in one or more other tables. In a data warehousing environment, the join condition is an equi-inner join between the primary key column or columns of the dimension tables and the foreign key column or columns in the fact table.

Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9599/viewspace-472892/，如需轉載，請注明出處，否則将追究法律責任。

轉載于:http://blog.itpub.net/9599/viewspace-472892/