Comparison between B-Tree and Bitmap indexes of Oracle Indexes

B-tree Indexing is a complete technology for all large-scale relational databases and is also the default Indexing Technology for Oracle databases.

Base Number: the number of different key values in the column you want to create an index. For example, if our column is gender, its key value is male and female, so your index base is 2.

In oracle, each table row has a rowid to mark the location of the row in the database.

About indexes:
B-TREE index with the following structure:
Root
/| \
Branching ........
/| \......................
Leaf1 ......

The leaf node structure is as follows:
| Index header | Column Length | column content | rowid (s) |

The size of a leaf node is about 8192*8 bit. Therefore, if the length of a field is 50, a node can be divided into 100 subnodes, and 1 million of the records are recorded, you only need three levels of nodes to complete the index. Therefore, generally, the depth of B-tree cannot exceed four levels. In this way, you can find a record based on B-tree, you only need to traverse up to four nodes to find the rowid, and then find the disk based on the rowid to get the final record data.

If the number of results set records for a column query is usually less than 7%, you should add an index to the column. For B-tree, the where xx is null condition does not use indexes. Therefore, we recommend that you set the default value for this column to avoid null values in this column, similarly, if this column has a null index in group by, it may also be invalid.

Bitmap index:
The bitmap index structure is also a tree structure, but the structure of the leaf node is different from that of B-tree. The results of bitmap leaf nodes are as follows:
<Key1 start-rowid end-rowid bitmap>
<Key2 start-rowid end-rowid bitmap>
......
The content of bitmap is a 01 combination of 110010100011100001. Its length is the same as the number of rowids contained between start-rowid and end-rowid. In this case, if the content of the column corresponding to the 9th rowids in the range is key1, the value of the 9th characters in the bitmap corresponding to kei1 is 1, otherwise it is 0. Similarly, the size of each block is 8192*8, so the leaf node of a bitmap index can index about 10000 records.
Bitmap indexes are still valid for null fields. Whether the value of null is 0 in bitmap or whether the value of keyX is null must be verified. The bitmap index has a very good query effect on the or condition. It does not adapt to frequent changes in the value of the index column. If the value of the index column changes frequently, the bitmap index will be disastrous because it will lock the blocks of all the related leaf nodes to update the bitmap value. It is suitable for decision support systems.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12