Oracle Indexes

Indexes (B-Tree and Bitmap) can offer improved performance in obtaining specific rows over the default full table scan. Oracle retrieves rows in one of the following ways :

• By RowID - Mapping column data to ROWIDs for the columns of interest.
• By Full Table Scan

Indexes may improve performance of SELECT, UPDATE or DELETE operations. Indexes may degrade performance of data change (DML), because indexes must be modified in addition to the table.

B-Tree indexes

1. Default and most common index type.
2. Can be unique or non-unique and either simple (one column) or concatenated (multi cols).
3. Provides best performance on high cardinality (many distinct values) columns.
4. Offer methods to retrieve small number of interesting rows.
5. Can be used if any combination of the leading columns of the index are used in the SQL. For ex - The OE.INVENTORIES table has the index INVENTORY_PK on the PRODUCT_ID (leading) and WAREHOUSE_ID columns. We can use this INVENTORY_PK index with the following query:

SELECT SUM(quantity_on_hand) FROM oe.inventories WHERE

PRODUCT_ID = 3191 and warehouse_id = 3;

Following will also use index as PRODUCT_ID is leading column in INDEX.

SELECT SUM(quantity_on_hand) FROM oe.inventories WHERE

PRODUCT_ID = 3191

But below will not use index as WAREHOUS_ID is not leading column

SELECT SUM(quantity_on_hand) FROM oe.inventories WHERE

warehouse_id = 3;

Bitmap Indexes

1. Primarily used for decision-support systems or static data, because they do not support row level locking.
2. Like B-Tree indexes they can also be simple or concatenated.
3. Best used for low or medium cardinality columns.
4. Index is constructed by storing the bit-maps in the leaf nodes of a B-Tree structure. The B-Tree makes it easy to find the bitmaps of interest quickly.
5. Bitmaps are stored in compressed format, so takes less disk space comparison to B-Tree index.