Re: Force index usage

Jacob Salomon <jake@apparel.net> wrote: :Tim Kelly wrote: : :> Ok, how in 7.x can I force an index to be used in an SQL Select :> statement? By using update statistics with medium and/or high as appropriate and sometimes a little beyond what is documented. You may find it necessary to do update statistics medium or high even on non indexed columns in some cases. See further comments below. :First the good news: This is a popular feature request at Informix. :The bad news: Last I heard of it, Informix's attitude was to deny this :kind of request. The optimizer should always be picking the best index :for the query; if it does not, please report a bug. What happens then? :Speculations anyone? : :It is possible that Informix will now claim "there is no ANSI standard :way to instruct the engine on what index to use". This is correct, of :course. Indexing is an implementation issue; the theoretical aspects of :the database survive without indexes. : :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. I am much more doubtfull to this "feature" request. In Oracle there are such options. If you look at the resulting SQL it's something I for sure wouldn't want to have in my application. It would be a total maintenance nightmare as it indeed is in some Oracle applications as I understand it. This far it's an advantage for Informix that we avoide this. Of course you are right that we have immediate bussiness solutions to solve and when the optimizer goes wrong we have a problem. During more than 10 years of writing thousands of SQL statements, more and more advanced ones, I have however yet to come over any situation that I couldn't solve. Either by programming arround it, or using the update statistics correctly. Lately (in 7.x) most issues can be solved by update statistics when the sql is ok and the indexes are in place. Mylargest database so far isn't more than a few gigabyte, so the issues may be different for larger databases. Now you may claim that you only want an option that you can use, and I can leave be. I don't need to use it. That may be so, and it's an OK argument. One problem however is it would diminish the pressure on Informix to create other better solutions to this problem. Again if you look at the Oracle world they have put in this and a pletora of other "tuning" options of all types and kinds. In effect they leave it up to the user to get the performance out of their engine. It can be done, but you have to work very hard at it. In my opinion the whole idea of a relational database (or a very significant part of it at least) have failed when this kind of "tuning" is necessary. I don't want Informix to fail in this way. I would rather that Informix spend their time and effort in other areas than putting in such features. If the lack of them makes Informix unusable for some purposes I would understand and accept that they put them in anyway. If they should ever do it they obviously have to copy the exact syntax Oracle is using for this kind of thing. Anything else would be too stupid. They may like or dislike the way Oracle have done it, they may even have better ideas giving more functionality, the issue is however one of establishing standards which goes beyond features and performance to save us all from total caos. 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