how can we choose that on which column is to make index to make search more efficient

You look at the WHERE clause of you queries. Columns mentioned there are good candidates for indexes.

Andy

Are you interested in the CREATE INDEX statement?

DB2 (unlike some other databases) has an excellent optimizer, and should chose the correct index so long as it has accurate statistics (from a runstats command). If you don't think that DB2 is chosing the correct index, then open a problem with IBM support.

If DB2 choses the wrong access path because the number of rows in the table varies drastically within a short timespan, alter the table and set it to volatile.

hi ankur,

the Db2 design advisor can suggest you on on which indexes u can create for better performance.

db2advis -d database -s "sql statment"

u can get more about design advisor by refering this

http://www.ibm.com/developerworks/db...arthur/#design

Hi,

You can actually see the columns on which the maximum number of hits are being done and decide the columns that require an index.