INDEXING

NEED FOR INDEX

           Its quite known that indexes makes DB2 SQL engine life easier to retrieve data. So creating right Indexes is the key to faster queries. I have developers coming to me everyday saying that their query is slow in  production and ask me to create or suggest the appropriate Indexes. Sometimes they even come up with Indexes pertaining to their query. The thing that developers forget is that their query is not the only one which is running in DB or making use of that particular table(s). So when DBA are creating indexes please keep the following things in mind

1. Size of table, If the table is really small (may be thousand to 10 thousand rows) there is no need of index on the table, it will just consume the disk space. In these cases table scans are faster. One exception is if the query is running several times a day you can create index.

2. No of Indexes, look at the number of indexes on the table. If there are many indexes already present do not over head database and table creating more indexes. It will slowdown inserting and updating into table.

3.single column index, these indexes solve purpose in a broader sense but when they are query specific it is of very less use. It is always preferable to create multiple column indexes which reduces the overhead and also makes query time faster.

4. Cardinality, It is very important that cardinality of the index is high. Cardinality of index can be reffered as no of unique rows in a index. If the % cardinality is less (i.e index cardinality/ Tables cardinality * 100) than 5 % then the index is considered very bad. The cardinality can be obtained from syscat.indexes table. FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD are the cardinality columns. FULLKEYCARD gives the index cardinality.

5. Explain, visit the explain plan for query multiple times before deciding the index. First make sure of the problem and then see if really indexing can solve the problem. Do not create index just to soothe someone and do not be in a prejudice thinking index creation will solve all the performance problems.

6. Planning, decide the no of columns and their order. If all the columns are not in a right order then engine does not make use of the index.

7. Advice plan, DB2 comes with a inbuilt tool called db2advis which generate an advice plan for the query which includes details of all the indexes need to be created to make query faster. Please make sure you only create main index. Advice plan suggests all possible indexes irrespective of overhead on database.

click here to navigate to Create Index statement

click here to navigate to Alter Index statement

DOWNSIDE

            Indexes are to database, what antibiotics are to body. When taken in prescribed dose will reduce the aliment, but when overdosed makes system week. More the number of index more is the overhead on system resulting in slowing down of insert and update process and also consuming more space in server.

    ***Please do not forget to send your suggestions on Indexes and FeedBack!! Happy Indexing!!