Re: Forcing the use of an index

At 08:30 AM 7/9/04, Richard Harnden wrote: >You're almost always better off leaving the choice of index to the >optimiser. The important thing is to have *decent* statistics. > >-- >rh The one place where I'm still left resorting to directives is in the case of functional indices (e.g. indices built on a user-defined routine). If I have a table mytab (mycol integer, othercols, ...) and an index built on myfunc(mycol), how do I calculate statistics on the myfunc(mycol) values so that the optimizer will be able to judge whether to use this index? Mike sending to informix-list

Mike Dunham-Wilkie wrote: > At 08:30 AM 7/9/04, Richard Harnden wrote: > >>You're almost always better off leaving the choice of index to the >>optimiser. The important thing is to have *decent* statistics. >> > > The one place where I'm still left resorting to directives is in the case > of functional indices (e.g. indices built on a user-defined routine). If I > have a table > > mytab (mycol integer, othercols, ...) > > and an index built on myfunc(mycol), > > how do I calculate statistics on the myfunc(mycol) values so that the > optimizer will be able to judge whether to use this index? > Good point. You can't, not really. You can give a per-call/selectivity cost in the with-clause, to nudge the optimiser in the right direction, but that's about it. -- rh