Shulou(Shulou.com)06/02 Report--

The bitmap index has two structural characteristics different from the traditional B* tree index: one is that there is a possible index column value corresponding to a leaf node on the leaf node. The other is that the leaf node uses a bitmap vector to indicate whether the corresponding row determines the index value.

Using bitmap vectors to record the values of corresponding rows can not only save storage space, but also improve the utilization of index results with the help of the fast characteristics of computer bitmap operation. Next, we analyze the situation by simulating the situation.

Bitmap Index simulation description

Suppose there is a data table T with two data columns An and B with the following values.

Serial number

A

B

one

L

one

two

T

two

three

L

two

four

M

one

Build bitmap indexes on two data columns An and B: idx_t_bita and idx_t_bitb. The storage logic structure for the two indexes is as follows:

Idx_t_bita index structure, which corresponds to the leaf node:

Index key value

Initial rowid

End rowid

Bitmap vector

L

Xxx

Ttt

1010

T

Xxx

Ttt

0100

M

Xxx

Ttt

0001

Idx_t_bitb index structure, which corresponds to the leaf node:

Index key value

Initial rowid

End rowid

Bitmap vector

one

Xxx

Ttt

1001

two

Xxx

Ttt

0110

For the query "select * from t where bread1 and (axiomatic L' or aegis M')"

Analysis: the use of bitmap indexes is very different from that of B* indexes. The use of the B* index usually starts from the root node and is compared continuously from the branch node to the nearest qualified leaf node. Through the continuous Scan operation on the leaf node, the result set rowid is scanned.

Bitmap indexes work in a very different way. Through the direct bit operation (and or) of different bitmap values, the result set vector (the calculated result) is obtained.

For instance SQL, you can split it into the following operations:

1. L' or adept M'

Axil: vector: 1010

Vector: 0001

The result of the or operation is the OR of two vectors: the result is 1011.

2. The vector combined with bicon1

Intermediate result vector: 1011

Back1: vector: 1001

Result of and operation, 1001. The first and fourth lines are the query results.

3. Get the result rowid

You now know the start rowid and end rowid, as well as the results of the first and fourth behavior actions. The result set rowid can be obtained by trial calculation.

The above operation calculation process illustrates two problems:

Bitmap indexes can be operated together by multiple indexes, unlike B* tree indexes, only one will join the result set.

The work of bitmap index is through bit logic operation, non-scan operation.

Practical test

Next, we will further observe the results through a series of experiments.

Construction of experimental environment

SQL > create table t as select owner owner1, object_type type1, owner owner2, object_type type2 from dba_objects

Table created

SQL > desc t

Name Type Nullable Default Comments

--

OWNER1 VARCHAR2 (30) Y

TYPE1 VARCHAR2 (19) Y

OWNER2 VARCHAR2 (30) Y

TYPE2 VARCHAR2 (19) Y

SQL > create index idx_t_owner1 on t (owner1)

Index created

SQL > create index idx_t_type1 on t (type1)

Index created

SQL > create bitmap index idx_t_owner2bit on t (owner2)

Index created

SQL > create bitmap index idx_t_type2bit on t (type2)

Index created

SQL > exec dbms_stats.gather_table_stats (user,'T',cascade = > true)

PL/SQL procedure successfully completed

Conventional index experiment

In the environment we built, the fields and types are exactly the same. The difference lies in the type of index used. Let's start with a regular indexing experiment.

/ / to prevent the execution plan from being affected, disable Bitmap Index first

SQL > alter index idx_t_owner2bit unusable

Index altered

SQL > alter index idx_t_type2bit unusable

Index altered

SQL > set pagesize 1000

SQL > set linesize 1000

SQL > explain plan for select * from t where owner1='SCOTT' and type1='TABLE'

It has been explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 2154532428

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 28 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_T_OWNER1 | 28 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("TYPE1" = 'TABLE')

2-access ("OWNER1" = 'SCOTT')

15 rows have been selected.

Note: there are B* indexes on both owner1 and type1, and both appear on where conditions. The result is the Scan approach, and only one index object is used.

Bitmap Index index experiment

This time the Bitmap Index column is used to correspond to the query criteria.

/ / Index processing

SQL > alter index idx_t_type2bit rebuild

Index altered

SQL > alter index idx_t_owner2bit rebuild

Index altered

SQL > alter index idx_t_owner1 unusable

Index altered

SQL > alter index idx_t_type1 unusable

Index altered

SQL > explain plan for select * from t where owner2='SCOTT' and type2='TABLE'

It has been explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 244872826

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 61 | 1708 | 13 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 61 | 1708 | 13 (0) | 00:00:01 |

| | 2 | BITMAP CONVERSION TO ROWIDS | | |

| | 3 | BITMAP AND | | |

| | * 4 | BITMAP INDEX SINGLE VALUE | IDX_T_TYPE2BIT |

| | * 5 | BITMAP INDEX SINGLE VALUE | IDX_T_OWNER2BIT |

-

Predicate Information (identified by operation id):

4-access ("TYPE2" = 'TABLE')

5-access ("OWNER2" = 'SCOTT')

18 rows have been selected.

In a SQL, both Bitmap indexes are used.

Let's experiment with a more complex condition.

SQL > explain plan for select * from t where owner2='SCOTT' and (type2='TABLE' or type2='INDEX')

It has been explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 3499411373

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 3416 | 24 (5) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 3416 | 24 (5) | 00:00:01 |

| | 2 | BITMAP CONVERSION TO ROWIDS | | |

| | 3 | BITMAP AND | | |

| | * 4 | BITMAP INDEX SINGLE VALUE | IDX_T_OWNER2BIT |

| | 5 | BITMAP OR | | |

| | * 6 | BITMAP INDEX SINGLE VALUE | IDX_T_TYPE2BIT |

| | * 7 | BITMAP INDEX SINGLE VALUE | IDX_T_TYPE2BIT |

-

Predicate Information (identified by operation id):

4-access ("OWNER2" = 'SCOTT')

6-access ("TYPE2" = 'INDEX')

7-access ("TYPE2" = 'TABLE')

21 rows have been selected.

Please note that the Bitmap series of and and or operations are the same as the simulations we did at the beginning.

Bitmap Index is an index type with narrow range of adaptation but strong pertinence to special effects. It is suitable to be used on some occasions and can bring unexpected results.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.