Tips for Creating and Managing Indexes In Oracle Database

Tips and Guidelines for Managing Indexes In Oracle Database

Tips and Tricks for Query Optimization | How to improve the execution of a query? | Best tips and ideas for creating indexes in Oracle

Tips for Creating & Managing Indexes In Oracle Database

Indexing in Oracle database is a very useful feature and one must definitely use the same to fasten the data retrieval process. It works just like suppose you open a book, you look into the index for the topic you want to read and instantly you get the page number. In the very same manner, indexes help in fetching the rows quickly inside Oracle Database. This article here is to give you some important guidelines which you should keep in mind when you are dealing with Indexes inside Oracle Database.

Recommendation and Tips for Creating and Managing Indexes in Oracle:

1) Always create indexes after you have inserted or loaded the data into the tables.

2) Index the correct tables and columns. The table with limited number of rows doesn't need to be indexed. The same concept goes with the columns. The Columns with a lot of NULL Values doesn't need to be indexed.

Below mentioned columns are suitable for indexes:

Very high number of unique values
Very wide range of values (good for B-Tree)
Very less range of values (good for BITMAP)


3) Always try to use Indexed columns in any joins or any filters you are using in your SQL statements.

4) Keep in mind the order of columns when you are going to create a composite index using grouped columns. Make sure you have put that column at first position which is frequently going to be accessed.

5) Try to limit the number of indexes on each table. Only create them when required.

6) Drop indexes which are no longer required to free up the storage space from datafiles.

7) If possible, try to store indexes in different tablespace than where the tables are stored. That will help in reducing disk contention.

8) If possible, using NO LOGGING clause while creating indexes. That will help in fast index creation. This helpful when creating indexes for very large tables.

Note:- Don't forget to take the database backup after you create the indexes since the creation info will not be archived after the use of NO LOGGING clause.

9) Drop the indexes before rebuilding them to avoid wastage of storage space.

I hope this helps !!