Re: Force index usage

In article <5omp75$bfh@cssun.mathcs.emory.edu>, "Vijay R. Anisetti" <rambabu@sprynet.com> writes >Richard Stanford wrote: >> >> > In article <5mhoc2$g0c@news.informix.com>, Amitabh B Sinha >> > <amitabh@informix.com> writes >> > >The next release of ODS and IUS will include a feature called >> > >directives, which will be a non-ANSI compliant way of directing >> > >the optimizer to do things it would not do in normal circumstances. >> > >The current set of directives include the following: >> > > forcing index selection, >> > > forcing full-table scans, >> > > forcing join method selection >> > > forcing join order >> > > etc. >> >> Am I the only one who is shuddering at this? >> ><<snip>> > >Informix "optimizer" does not always work (IMO). I have experience with >a table of >about 2.5 million recs, a simple SELECT on an indexed column, the >'optimizer' was >not using the index (with OPTCOMPIND=2). But with a OPTCOMPIND=0 in the >configuration >file and re-initializing the server, it started using the index and as >expected >it is much faster. So, I had to change the configuration of the server >to get it >use the index. Since, it was one of the instances which is very less >used, it wasn't >a problem. > > Anyway, bottom line is, it will be great if there is an option to >'direct' the >optimizer. Asking for more, IDTS. ;-) > That is what OPTCOMPIND (OPT-imizer COMP-are cost of IND-icies) 0 = Indexes are better than sequential scans (Dynamic Hash Joins) 2 - Sequential scans (Dynamic Hash Joins) are better than indicies. See, give users a configration option for the optimizer and they immediately say the Informix optimizer is crap! I would stay away from being able to force index usage... > Have a good rest of the day! > -- David Williams