Re: Force index usage

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. > >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. [snip] I designed/built a 180 table database using Progress (don't ask why). We HAD to use USE-INDEX statements in our 4GL code to get performance; Progress' optimiser is braindead and would consistently use the wrong index when there were multiple indexes on a table. Speed improvements in the orders of magnitude by overriding the optimiser. BUT - by doing this, you've just intimately tied your APPLICATION to your database design. Change the indexes and your code dies. This is a major ongoing maintenance headache. I only agreed, reluctantly, do do this in a few key areas where we absolutely had to have the performance and 3 Progress DBAs (including me) agreed that the design was correct and that we had no choice. But I still don't like it. Flip side - a lab system I designed using Informix was getting severe performance problems when it went over 3 million rows in a table. Update statistics didn't help. I cut out the SQL, pasted it into dbaccess, set EXPLAIN on and analysed what the optimiser was doing. Sure enough, it needed another index. I added that, and without doing anything else (OK, I re-ran UPDATE STATISTICS) the offending program's execution time dropped from over 15 minutes to less than 30 seconds. Anyone who embeds database design level instructions in their applications is asking for trouble. Crutch for either a shit query optimiser or a poor design (or both). IMO. Peter Wiley