Wednesday, December 2, 2015

Difference between B*Tree and Bitmap Index in Oracle.

B*Tree index & Bitmap index is very different, but in functionally they are identical while retrieving rows faster and avoiding a full-table scan. These indexes are used mainly for performance tuning, which turns in retrieving data quite fast.

B*Tree Index
Bitmap Index
A type of index that uses a balanced tree structure for efficient record retrieval.
A type of index that uses a string of bits to quickly locate rows in a table.
B*Tree index stores key data in ascending or descending order and very useful for OLTP.

Bitmap indexes are normally used to index low cardinality columns in a data warehouse environment, useful for Decision Support System.
A B*Tree index is used most when the cardinality is high and it is a default Index type.
We can use Bitmap index where there are a lot of duplicate data in the indexed column (for example Gender).
A B*Tree index does not includes any specific keyword while creating it.
create index person_region on person (region);
A bitmap index includes the "bitmap" keyword while creating it.
create bitmap index person_region on person (region);
B*Tree index is an index that is created on columns that contain very unique values.

A bitmap index generally consumes less space stored in db as a highly compressed index format.

B*Tree index is very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID.
A bitmap index used on a table having low insert./update/delete (DML)  activity. Updating a bitmap index takes a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.
A B*Tree index has index nodes (based on data block size), it a tree form:
Internally, a bitmap index consists of 4 columns, first - the index value, the second and third column consisting of the start and last rowid of the table, and the fourth column consisting of the bitmap.
A B*Tree index stores the index value and the physical rowid of the row. The index values are arranged in the form of leaves.
A bitmap index looks like this, a two-dimensional array with zero and one (bit) values.
In a B*Tree Index all the lower values are placed on the left side & Higher Values on the Right Side.
A bitmap index can cover a few thousand rows in a single block. When you are updating the indexed column, Oracle takes an exclusive lock on the index slot for the duration of the transaction.
A regular B*Tree index covers maybe a few hundred table rows in a single index leaf block. In a regular B*Tree index, this affects just the actual row being updated because each slot in the index covers only a single row. 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.
In a bit mapped index each slot covers a range of rowid's, so more table rows are locked. The chances of two different processes colliding (and deadlocking) when they are doing bulk updates are increased.
B*Tree index is good choice for most uses:
  • maintain the sort order of the data, making it easy to look up range data
  • multicolumn indexes: you can use the leading edge columns to resolve a query, even if that query doesn't reference all columns in the index
  • they automatically stay balanced
  • performance remains relatively constant
  • can also specify reverse and unique
  • They are very fast when you are selecting just a small very subset of the index data.
  • They work better when you have a lot of distinct indexed values.
  • Combining several B*Tree indexes can be done, but simpler approaches are often more efficient.
  • They are not useful when there are few distinct values for the indexed data, or when you want to get a large subset of the data.
  • Each B*Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.
A Bitmap index used in below scenarios:
  • Are a more specialized index variant:
  • Use them to index columns with that contain a relatively small number of distinct values
  • They are compact, saving space
  • Were designed for query intensive databases, so not recommended for OLTP databases
  • Not good for range scans
  • Are available only in Enterprise Edition.
  • Mostly created on Transaction Tables on which the data is continuously being added. They are very inefficient when inserting/updating values.
  • They encode indexed values as bitmaps and so are very spaces efficient.
  • DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.
  • Mostly used in data warehouse applications, where the database is read only except for the ETL processes and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values.
  • 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.