IBM Mainframe Forum

How do we decide which columns to index in a db2 table so that cost will be less and performance will be more?

Hello,

By analysing the use of the data. . . Data items that are normally used in the WHERE (predicates) are usually good candidates while quantities and amounts are not good candidates.

Your dba or someone senior on your project will be able to provide specifics.

Hi Shruthi,


If you define more indexes ( Example 5 out of 10 cols ) on a table , it helps when you fetch the rows from table.
But excessive use of indexing affects the performance of the table when you update or insert into the table.

As a programmer , all we can do is SQL performance tuning and DBA does the DB2 performance tuning.
Indexing is one of the performance tuning technique , but one should be cautious as excess use of it is bad..

Let me give an example :

Suppose you have a table with 5 cols and Emp Id is one of the col's :

When you define primary key as Emp id , Index is automatically created for Emp id.
Hence you dont need to create a index exclusively.

Suppose you are using some other column name( other than prmary key ) from the same table in most of
youe SQL queries to fetch the rows , and teh table is extremely large , then you can go for indexing
that column too.

Let me know if you need any other info..

Regards
Uday