B-Tree and Bitmap Index

There are two types of indexes used in Oracle. These are B-Tree and Bitmap. These indexes are used for performance tuning, which in effect makes looking for records and retrieving them quite fast. The index functions create an entry for all values that appear in the indexed columns. B-Tree indexes are the type that are used by OLTP systems and which are mainly implemented by default. Bitmap, on the other hand, comes as a highly compressed index format which, in most cases, is employed in data warehouses.

Bitmap can in general be referred to as a method of indexing though which performance benefits and storage savings can be sought. Its use, as earlier stated, is mainly in the data warehousing environment. The reason for this is because the updates of data are not as frequent and ad hoc queries are more in the environment. In the implementation of Bitmap, low cardinal data is preferred. Bitmap is the preferred choice for column items that have low options such as gender, which will have 2 values only and are preferred. Static data in the warehouse is also a good characteristic of data that would be excellently implemented using Bitmap. Another characteristic of Bitmap is a stream of bits whereby each bit is implemented to a column value in a single row of a table.

A B-tree index, on the other hand, is an index that is created on columns that contain very unique values. The B-Tree index has entries ordered where each entry has a search key value and a pointer that refers to a given row and value. In the event that a server finds a matching constraint that relates to the value in question, the pointer is deployed to fetch the row.

One of the differences between the two is that there is low duplication and a high co-cordiality in B-Tree while the opposite happens in Bitmap. Bitmap has high duplication instances and low cordiality. The Bitmap index is seen to be advantageous over B-Tree index, as it has tables that have millions of rows as the specified columns have low cardinality. The indexes in Bitmap, therefore, offer a better performance as opposed to the B-Tree indexes.

B-Trees seem to be extremely fast when a small data set is collected, in most cases, the data ought not exceed 10% of the database size. These two work together when there are a lot of distinct values of which are indexed. It is also unique to B-Tree that several indexes can be merged to produce a very efficient program. Bitmap, on the other hand, tends to work best when there are lower indexed values for maximum efficiency.

B-trees are poor when it comes to looking for larger data subsets that exceed 10% of the subset data. Bitmap takes on this challenge to deliver high-quality results, as it works better when there are few distinct values.

If there are many indexes in a busy table using B-Tree, a problem can arise as a result of small penalties imposed when inserting indexed data or in the event you need to be inserting and updating indexed data. This is not a problem with Bitmap, as it is very efficient in inserting and updating values, whatever the size that is in question.

Summary

B-Tree and Bitmap are two types of indexes used in Oracle
Bitmap is a method of indexing, offering performance benefits and storage savings
B-Tree index is an index that is created on columns that contain very unique values
B-Tree works best with many distinct indexed values
Bitmap works best with many distinct indexed values

Structural difference between Bitmap and B-Tree index in oracle.

Structural difference between bitmap and B-Tree index

B-Tree
It is made of branch nodes and leaf nodes. Branch nodes holds prefix key value along with the link to the leaf node. The leaf node in turn contains the indexed value and rowed.



Bitmap
It simply consists of bits for every single distinct value. It uses a string of bits to quickly locate rows in a table. Used to index low cardinal columns.