Re: Force index usage

news_reader@mew.corp.sgi.com (Pablo Sanchez) wrote: : :In article <3385cf02.28251463@gate.idg.no>, Nils.Myklebust@idg.no writes: :> :Still, y'know, folks use the database to run a business and sometimes :> :the heuristic rules in the optimizer *do* screw up. It would be helpful :> :if Informix gave us a non-ASNI compliant way to set the optimizer :> :straight. If we mess up for failure to keep statistics up to date, :> :we'll accept the blame. Just give us that &^%$! feature!! Please.. :> : :> :Thanks for the steam valve, Tim. :> :> [ stuff snipped about it being an "advantage" to *not* be able to :> force an index ] :> : :I find your argument unconvincing because even though I agree with :the academic nature of your points, the fact of the matter is that :software has bugs. Why *wait* for Informix Inc. to *fix* the bug and :have you jump through all sorts of gyrations rather than letting the :SQL coder place a fix in until Informix Inc. can get their problem :fixed? : :This way you have the best of both worlds: a good optimizer and :real-world realization that software has bugs and that an RDBMS :vendor needs to *help* the developers to "get the job done." There may be two reasons why you want to be able to force the use of spesific indexes: 1. You have done update statistics, set optcompind, reprogrammed your sql and done everything else required and still can't get your sql to perform as expected using the best available indexes. 2. You "know" how the indexes should be used. You can see they aren't used that way. You simply want to tell the engine what to do. In the first case post the real problem. Describe the tables involved, post the actual sql used and describe what you have done to try to optimize it. It's highly likely there is a solution. I haven't heard of problems that aren't possible to solve, but they may sure be there. Let's have proof however before we ask for such a feature that is so problematic. If number 2 is your point of view you are for sure creating more problems for your self than you can currently immagin. :-- :Pablo Sanchez | wk: 415.933.3812| pg: 800.930.5635 -or- pablo_p@pager.sgi.com :--------------+-----------------+-------------------------------------------- :pablo@sgi.com ... when mailing me, place "not spam" in the Subject 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