Bitmap Index

摘自http://www.itpub.net/viewthread.php?tid=114023&extra=&page=2
1. How do we store a bitmap index?
2. Example
3. Bitmapped index blockdump
4. Miscellaneous

1. How do we store a bitmap index?

Essentially this is a three-step operation:

I. A bitmap is created for every distinct key column value.
II. The bitmaps are then compressed.
III. The compressed bitmaps are stored in a normal B-TREE index
structure along with the keyvalue, and a ROWID range. The
ROWID range is the range of ROWIDs in the base table to which
this bitmap applies.

2. Example

This is best illustrated with an example. The standard EMP table
has been modified to include a politically incorrect GENDER column.
GENDER is a CHAR(1) datatype and takes values 'M' or 'F'.

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
GENDER CHAR(1),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));

The table has 14 rows inserted into it:

INSERT INTO EMP VALUES
(7369,'SMITH','M','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','M','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','M','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','F','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','F','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','M','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','M','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','F','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','F','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','F','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','M','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','F','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','M','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','F','CLERK',7782,'23-JAN-82',1300,NULL,10);

A bitmap index, EMP$GENDER$BMIDX is created on EMP (GENDER):

CREATE BITMAP INDEX EMP$GENDER$BMIDX ON EMP (GENDER);

This is how the bitmap index is stored internally:

I. A bitmap is created for every distinct key column value. In
this case, there are 2 distinct values, 'M' and 'F'. If the
rows are stored in the order above, then the 2 bitmaps will
look like this:

'M': 11100110001010
'F': 00011001110101

(Note, you XOR the bitmaps for all the distinct key values,
you will end up with a bitmap of '1's).

II. The bitmaps are then compressed (see (4))

III. The compressed bitmaps are stored in a normal B-TREE index
structure along with the keyvalue and a ROWID range. The
ROWID range is the range of ROWIDs in the base table to which
this bitmap applies.

3. Bitmapped index blockdump

In the above example, 2 B-TREE index entries are created, one
for each of the unique index keys. A dump of the index block
shows:

<< lines cut here >>
row#0[1866] flag: ----, lock: 0
col 0; len 1; (1): 46
col 1; len 6; (6): 01 00 00 0d 00 00
col 2; len 6; (6): 01 00 00 0d 00 0f
col 3; len 3; (3): c9 98 2b
row#1[1844] flag: ----, lock: 0
col 0; len 1; (1): 4d
col 1; len 6; (6): 01 00 00 0d 00 00
col 2; len 6; (6): 01 00 00 0d 00 0f
col 3; len 3; (3): c9 67 14
----- end of leaf block dump -----

Notes:
col 0: key value (46 is ascii 'F', 4d is ascii 'M')
col 1: ROWID range lower bound
col 2: ROWID range upper bound
col 3: compressed bitmap for this key

In this example, the bitmaps for both keys point to rows 0 through
15 in DBA 0x0100000d (the datablock in EMP containing the rows).

4. Miscellaneous

4.1. There can be more than one index entry per distinct key

The above example is a very simple case. Because of the small
number of rows, the bitmap index contains one B-TREE entry per
key value. If the table was large, and/or if the key values in
the table were clustered poorly, there would be multiple index
entries per distinct key value.

4.2. Analyzing bitmap indexes

Be wary about statistics produced by analyzing bitmap indexes.
For example, we recently had a call from someone who had
analyzed a large table, and USER_INDEXES.NUM_ROWS reported
substantially fewer rows in the bitmap index than in the base
table. This is expected - analyze would have simply calculated
the number of B-TREE entries.

4.3. Bitmap indexes, locking and concurrency

Bitmapped indexes are not suitable for key columns that are the
subject of DML. The reason for this is that if the key is
modified, the index associated index entry (the bitmap) must
also be updated, and therefore locked for the duration of the
transaction. Whilst the index entry is locked, any other updates
on the key column (for rows 'covered' by this bitmap) are
prohibited.

The extent of the problem depends entirely upon how many index
entries exist per distinct index key. If all rows for this key
are 'covered' by one bitmap (and therefore one index entry)
then a transaction that updates this key value in the base table
will block all other updates of this value until the transaction
commits or rolls back.

4.4. Compression

To Be Written. Please 'Add Remark' if have information to be included
in this section.

[@more@]