Indexing Strategy in DW: What are Indexes, Types and Significance ?

We are starting off a new series on Indexing strategies in an Oracle Data warehouse. Before going into detail let us first see what an Oracle Index is. 

A database index is a database object that is created to optimize the performance of a read/select statement on a particular table. They greatly reduce the I/O operations on a table by reducing the rows processed. 

Oracle defines Indexes as below, 

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

'Indexing' is a term coined for the strategy used for indexing in a particular relational DBMS. Let us see the advantages of indexing

1. Avoid full table scans and there by reducing the cost and time to retrieve from a database table.
2. They avoid sorting of oracle queries. which in turn reduces the time for retrieval of data. 

There are different kinds of Indexes available in Oracle and they are the following, 

• B-Tree or Normal Indexes
• Bitmap indexes
• Function based indexes

B-Tree Indexes are the most common indexes in oracle database. by default a B-Tree index is generated. Here values are stored as leafs. There are branch blocks and leaf blocks. Branch blocks contain the leaf blocks where actual pointer values are stored. Here one entry in an index points to one row.

Bitmap indexes are usually created for low carnality columns like Gender, flags etc. In Bitmap indexes one index entry points to multiple rows. 

Function based indexes are indexes created on columns with a function. If one was to apply calculations on a particular column then function based indexes are created on that particular column.