Index Optimization Tips in DB2

Indexes are the data base objects which are stored physically on Disk, it contains the address of the actual data stored in tables that helps to retrieve the data quickly from the database. This can be considered as the ordered set of pointers which points to the data in actual table. These are created on tables to improve the performance by retrieving the data faster.

These are the most vital part in query performance so to create an ideal database, these needs to be created carefully to get the optimum performance. Below are few tips for the index optimization.

1. Index Creation – Use columns that are mostly used as predicate for index creation.Analyze the Columns usage carefully and chose the best ones to be part of it.
2. Key Size – Give the optimum index Key size, Make sure that it is not too long.
3. Unused Indexes -Drop the ones that are not in use to free up the disk space used by them.
4. Index Column Data Type – Integer data types are the best ones as it occupies less space thus decreases the number of index pages.
5. Index Type – For the Sqls that return a range of values, Use Clustered Index. For SQLs that return limited number of rows use non-clustered index. Also make sure to create a clustered index on all tables.                                                         Note: Only one Clustered Index is allowed per table, There can be 249 non-clustered indexes on a table.
6. Modify the Indexes – Identify the indexes that under perform and has less column selectivity. Add columns so to improve match query to index column match.
7. Optimum number of Indexes: Each index costs, so limit the number to as minimum as possible..
8. Order of Index Columns – Give the correct order to the columns that are part of Index. Column which has high possibility of matching should the first column in Index Key followed by lesser possible columns.