ITPub博客

首页 > 应用开发 > IT综合 > Cardinality (222)

Cardinality (222)

原创 IT综合 作者:tsinglee 时间:2007-11-19 11:24:22 0 删除 编辑

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@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10599713/viewspace-982966/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    740
  • 访问量
    1897858