Bitmap , Btree and B+ tree indexes:

B-Tree indexes (Normal)

By default, Oracle creates B-tree indexes As the name suggests it makes use of the B-Tree parsing method to find the value being searched for. When it gets a value to be searched, it gets the nearest possible index value. Then it goes to the left node if the value it lesser or to the right if the value is higher. It goes on doing this until it reaches the leaf node which contains the value it is searching for.


How to Create an Index?

CREATE INDEX IDX_NAME
ON TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ; --optional

How to Rename an Index?

ALTER INDEX IDX_NAME
RENAME TO NEW_IDX_NAME;
How to Collect Statistics on an Index?

ALTER INDEX IDX_NAME

COMPUTE STATISTICS;
How to Drop an Index?

DROP INDEX IDX_NAME;

Bitmap indexes

Oracle Bitmap Indexes are different from Standard B-Tree Indexes in the way they store the information about the indexes. Bitmap Indexes stores the information about the value in bits; with 0s and 1s; thus called Bitmaps. Something like this:



Bitmap Index creates a two-dimensional array with one column for every row in the table being indexed. If the value being indexed is found in the row, it will specify a 1 and if not found then a 0 . So while searching, Oracle takes only the information from those rows which shows a 1 in the Bitmap Index. That means, the records which are pointed by the 1 s in the Index, have got the searched/indexed value.

How to Create a Bitmap Index?


CREATE BITMAP INDEX IDX_NAME
ON TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ;

DML Operations:

If we have too much DML operations (insert/update/delete) on a table on which the columns are indexed, it is better to use B-Tree indexes।Updating bitmapped indexes consumes lot of resources. So, it is better to use Bitmap Indexes on columns of Tables which are updated/inserted/deleted less frequently .

OLAP system and hence for Star scema: Bit map index is more useful.

OLTP and ODS system B tree index is more usefull.

Performance:

Compressed structure in Bitmap Indexes makes it faster to read from disk; but utilises more CPU to decompress the information.

Uncompressed structure in B-Tree involves a lesser CPU load compared to Bitmap but it requires more bandwidth to read since it is uncompressed data.

Cardinality:

Cardinality means, the ratio of distinct values to the total number of records. So, a low cardinality means, distinct values are very less.
In such a scenario, Bitmap indexes are considered to be more efficient thatB-Tree indexes.

ex indexing on primary key means lot of distinct values. hence after decompress also it will not take considerable less space so searching time will not be much lesser compared to B tree index.

Indexing on non primary key like gender will have less distinct values and hence Bitmap index will help here.

B+ tree indexes:

A B+-tree in certain aspects is a generalization of a binary search tree (BST). The main difference is that nodes of a B+-tree will point to many children nodes rather than being limited to only two. Since our goal is to minimize disk accesses whenever we are trying to locate records, we want to make the height of the multiway search tree as small as possible. This goal is achieved by having the tree branch in large amounts at each node.
The B+-tree stores records (or pointers to actual records) only at the leaf nodes, which are all found at the same level in the tree, so the tree is always height balanced.
Internal nodes store search key values, and are used only as placeholders to guide the search.