which columns to index



IBM's flagship relational database management system

which columns to index

Postby shruti » Sun Apr 11, 2010 7:50 pm

How do we decide which columns to index in a db2 table so that cost will be less and performance will be more?
shruti
 
Posts: 4
Joined: Sun Apr 11, 2010 3:16 pm
Has thanked: 0 time
Been thanked: 0 time

Re: which columns to index

Postby dick scherrer » Mon Apr 12, 2010 2:15 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: which columns to index

Postby uday123 » Wed Apr 14, 2010 6:40 pm

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
uday123
 
Posts: 29
Joined: Fri Feb 06, 2009 6:13 am
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post