Cardinality (222)

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. If 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.

集的勢(集數)
1. 在基數小的列上建立點陣圖索引效果最好。所謂某列的基數小是指此列中所有不相同的值的個數要小於總行數
2. 使用點陣圖索引能夠顯著地提高查詢效能。查詢的 WHERE 子句中的 AND 和 OR 條件直接對點陣圖進行布林運算得到一個點陣圖結果集,
而無需將所有的點陣圖轉換為 ROWID。如果布林操作後的結果集較小，那麼查詢就能夠迅速得到結果，而無需進行全表掃描。

[@more@]