Problem with indexes

Hi Group, We are experiencing a problem regarding the indexes of a table. The table has 10 fields a-j and we have two composite indexes ind1(c,e) and ind2 (c,d,e,f) and two separate indexes ind3 and ind4 on c and e resp. Now when we run a query having c and d in the where condition, it picks up index ind3(c,e) and even if the where condition includes all c,d,e and f, it picks up ind1(c,e) or ind3(c) and it NEVER picks up index ind2(c,d,e,f). We tried to force the index though the query, but the estimated cost was very high. I will really appreciate if anybody could explain why is this happening and if there is any way to make the query use index ind2(c,d,e,f) for the table. Thanks in advance, Rana Rana Ray (201) 9026194

Dear Rana. First, you have to analyse the selectivity of your indexes. Second. Have you issued an UPDATE STATISTICS statement after creating indexes. To fully understand your situation we need your sqlexplain.out file with the optimizer pathes of your query. If you have IDS 7.30 or higher, you can force optimizer to use the particular index with optimizer's directives. It's described in "Performance Guide". -- With best regards, Yuri Dovgart. > Hi Group, > > We are experiencing a problem regarding the indexes of a table. The table > has 10 fields a-j and we have two composite indexes ind1(c,e) and ind2 > (c,d,e,f) and two separate indexes ind3 and ind4 on c and e resp. Now when > we run a query having c and d in the where condition, it picks up index > ind3(c,e) and even if the where condition includes all c,d,e and f, it picks > up ind1(c,e) or ind3(c) and it NEVER picks up index ind2(c,d,e,f). We tried > to force the index though the query, but the estimated cost was very high. I > will really appreciate if anybody could explain why is this happening and if > there is any way to make the query use index ind2(c,d,e,f) for the table. > > Thanks in advance, > Rana > > Rana Ray > (201) 9026194 > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

First thing.... Have you run Update Statistics for the table? Terry Hillick CSCS, Inc. rray@painewebber.com wrote: > Hi Group, > > We are experiencing a problem regarding the indexes of a table. The table > has 10 fields a-j and we have two composite indexes ind1(c,e) and ind2 > (c,d,e,f) and two separate indexes ind3 and ind4 on c and e resp. Now when > we run a query having c and d in the where condition, it picks up index > ind3(c,e) and even if the where condition includes all c,d,e and f, it picks > up ind1(c,e) or ind3(c) and it NEVER picks up index ind2(c,d,e,f). We tried > to force the index though the query, but the estimated cost was very high. I > will really appreciate if anybody could explain why is this happening and if > there is any way to make the query use index ind2(c,d,e,f) for the table. > > Thanks in advance, > Rana > > Rana Ray > (201) 9026194