Re: Force index usage
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? NO! If people ring me to come and tune their performance problems, and I find they have set ANY of these new features, I'll charge TRIPLE! The Optimiser does what it was designed to do, and very well indeed IMNSHO. I tune systems to take advantage of the optimiser, not to work against it. How am I supposed to tune if people are forcing index selection or something similar?? > So far, in our 95% OLTP database (c. 750 tables), we've found that using > intelligent indexes (which we didnt' have until recently), etc, pretty > much ensures that the optimizer works properly. In 99% of the cases where > we've been annoyed at the optimizer, we've found the problems to be ours > rather than its. Exactly. > Our developers have been after a way to specify indexes for a long time, as > it means that they don't need to worry about poor index design (which they > used to do before we hired a full-time logical dba) or poor query construction. Lazy programmers? Never heard of them. :-( NOT ) > After many battles, we're just begining to convince people that the > database can do its job if its set up correctly (ie: replacing the 3-5 > indexes per table that all started with the same two columns, both of > which had one and only one possible value (aargh!)), this happens. It's a tough job isn't it? Especially when they are used to MS products that rarely do what they are designed to do. (Alegedly :) > My only concilation is that we're supposed to be careful (now) about not > using any features that would interfere with being "database-independant" > (This on a 1200 .4gl program system) so we can probably squash this when > it comes out. That, and the fact that most of our "less clueful" people > haven't yet figured out our internal newsgroups (let alone USENET) and > never bother to read the release notes, so will probably never find out > about them. A database-independant Informix-4GL system? Mmmmm. ;-) > [Sigh] Ditto that. ;-| Perhaps Informix should re-vamp their engines so that you just get a disk driver, and let the programmers do the rest. ;-) Cheers, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+