Re: Problem with indexes

thillick wrote: > > First thing.... Have you run Update Statistics for the table? Terry is right. You need to perform UPDATE STATISTICS MEDIUM on the table as a whole then UPDATE STATISTICS HIGH for columns c, e, and d separately with the DISTRIBUTIONS ONLY option and UPDATE STATISTICS LOW for each of the index key lists. My dostats.ec utility will do this for you. Rana, you do not specify what version of IDS you are using. If you have any 7.3x version you can use optimizer hints to specify your preferred index(es) or what indexes you want avoided. Keep in mind though that while I agree that in this case the chosen index does not seem optimal, and is probably the result of out-of-date or non-existent data distributions, in general the optimizer gets it right most of the time and it is really rare for us, as users, to do better if the stats are up-to-date. Art S. Kagel > 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