ITPub博客

首页 > Linux操作系统 > Linux操作系统 > The Secrets of Oracle Bitmap Indexes

The Secrets of Oracle Bitmap Indexes

原创 Linux操作系统 作者:wuxidba 时间:2011-06-16 10:42:28 0 删除 编辑

Overview

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

Characteristic of Bitmap Indexes

  • For columns with very few unique values (low cardinality)

Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 %  that the column is ideal candidate, consider also 0.2% – 1%)

  • Tables that have no or little insert/update are good candidates (static data in warehouse)
     
  • Stream of bits: each bit relates to a column value in a single row of table

create bitmap index person_region on person (region);

        Row     Region   North   East   West   South
        1       North        1      0      0       0
        2       East         0      1      0       0
        3       West         0      0      1       0
        4       West         0      0      1       0
        5       South        0      0      0       1
        6       North        1      0      0       0

Advantage of Bitmap Indexes

The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.

Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes

The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful.

Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1 Session 2

create table bitmap_index_demo (
  value varchar2(20)
);

insert into bitmap_index_demo
select decode(mod(rownum,2),0,'M','F')
  from all_objects;

 

create bitmap index
  bitmap_index_demo_idx
  on bitmap_index_demo(value);

insert into bitmap_index_demo
  values ('M');
1 row created.

insert into bitmap_index_demo
  values ('F');
1 row created.

insert into bitmap_index_demo
  values ('F');
...... waiting ......

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

insert into bitmap_index_demo
  values ('M');
...... waiting ......

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-05-12

  • 博文量
    64
  • 访问量
    64703