by Anjan Roy

Created on: June 20, 2009

Imagine yourself reading a book of over 500 pages. Someone comes and asks you, can you find out all the pages where the word 'Nature' is used? What would you do first? You have two options, one, go through all the pages, and find pages where the word 'Nature' is used, or the second, flip over to the end of the book, where you will look under the letter 'N' for the word 'Nature' and look for all the pages that are listed under that. Which search method is faster? Of course, searching through the indexes is much faster than going through every page of the book. In a database table, indexes do exactly the same thing. They help us in searching the data that we are looking for. In any database, it is necessary to build indexes on the tables in order to improve the performance of queries.

In a database, similar to the example that was given earlier, for every table data and indexes are maintained separately. The data block contains the actual data whereas the index block contains the pointers to the data block. Additional advantages of maintaining indexes are that the database can perform parallel reads. If the index blocks and the data blocks are on separete physical disks, then a lot of time is also saved on the I/O operations.

Indexes are basically of two types. They are categorized generally based on the way they are stored internally. They are B-Tree or BITMAP. B-Tree indexes are the indexes that are stored in form of a binary tree. So, when reading the index block of a B-Tree index, the database performs binary tree search. BITMAP indexes are the ones where a BITMAP of the unique values is stored for all the rows. The fewer the unique values are, the more efficient the BITMAP index is.

However, there are a couple of disdvantages of having indexes. First, when you are writing too many records at the same time either via a batch process or via a load process, for every row, the database will have to build index. This will significantly slow down the batch process or the load process. To avoid, generally, the indexes are dropped before these processes and built back when the load is done. The second disadvantage of the index is the choice of index on a small table. Imagine if a table is small enough to fit in one database block. Having indexes on such a table will require double the time to fetch results - one for scanning the indexes and the other for scanning the data. Therefore, it is not always necessary to create indexes on small tables.

Learn more about this author, Anjan Roy.
Click here to send this author comments or questions.