Re: Force index usage

> 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? 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. 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. 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. 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. [Sigh] -Richard #include specific disclaimer: These are my own opinions, as are any from this non-company account, and should not be considered the opinion of Dallas Systems.