Db2 Indexing

[etan1999]

Hi all.

My application is having timeout problem due to the length of time to execute a DB2 Select statement. It is going against a table, averaging about 18 million records with 22 columns. This sql statement has one WHERE condition, a date field which is NOT part of any index. I have asked our DBA if building an index based on this date field would improve the performance and he said no because of its DATE data type. My questions are :
1) Is an index on a DATE column NOT as efficient as an index based on a CHAR or NUMERIC field ?
2) Can you recommend another solution ?

Thank you so much.

 

[blom0344]

I would be pragmatic and do a little testing with an index on the date field. An explain plan will tell you quickly whether the addition of the index will increase performance.
Are there any other indices active, is there a primary key (must be). Can you extend the condition with a field that is part of the primary key?
If adding the index is not benificial, you drop the index altogether ..........
I think indices on integers are the most efficient performance-wise. Look into 'clustered indices' as well

T. Blom
Information analyst
tbl@shimano-eu.com

 

[etan1999]

T. Blom,
Thanks for the immediate response. Yes, there are 3 indices defined for this table but none include this date field. We are working with our clients to see if they would be flexible enough to do a search based on other columns too.