Re: Force index usage

peter@prospect.anprod.csiro.au (Peter Wiley) wrote: :In article <Pine.BSF.3.91.970623100415.21740D-100000@kilimanjaro.dallas.herald.net> Richard Stanford <richards@herald.net.REMOVE> writes: :>> 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? : :Nope, I'm horrified. Not horrified, but frightened. ...good arguments sniped... I can understand that marketing pressure makes Informix do this. If you study a few O... articles in the press there is a lot of discussions on optimising SQL. The O... optimiser seems not to have worked well in the past. I don't know how well it works now. People coming from that environement seems to be so used to having to control the optimiser and telling it quite detailed how to access the data that one can expect them to want similar fasilities in Informix. The newest Informix optimiser works well enough however that it's hardly ever usefull when comparing to all the maintenance problems it creates. I would however expect a lot of people to start using such fasilities. They are a seemingly simple way out of a problem. People seem to like "simple" solutions. They seem to think that any problems it creates will be someone elses problem. If this happens at even a relatively small number of sites, I forsee a time when we will start hearing how hard it is to tune Informix database solutions - that it isn't much simpler than the O.... solutions. A devastating result for Informix. A key issue for Informix to save themselves from such result is that their consultants and support people as well as the general users are strongly warned against using these fasilities. Hopefully Informix will include in their manuals a very strong warning with clear examples of the horrifying results of using these constructs in anything but the most extreme cases. I also sincerely hope Informix will continue putting greate efforts into further development of their optimiser. If the availability of these new fasilities becomes an excuse for relaxing these efforts we will be in for even more trouble. As I know Informix I do however expect that they will understand this, and we will see new strides in their optimising technology. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org