RE: Force index usage

The reason why I was asking was I'm moving from 5.x to 7.x and I was setting EXPLAIN ON and was expecting the result file to indicate it was using one of the indexes - The one I wanted. The table has two indexes and I was forcing, or what I thought, the proper index usage via the select statement. To my surprise the result file indicated it was doing a table scan. Currently, my test table only has a few rows but it will grow fairly large and therefor was the reason I wanted to check to ensure the proper index was being used. It's more for my peace of mind, what I wanted to see was the SET EXPLAIN ON result file ensure me that the index was being used. So, what I wanted was a statement to 'FORCE' an index to be used. Silly me, thinking that Informix would have added this feature. Again, it's like Informix's attitude toward other things; they always respond with 'well that would be dangerous and you could mess-up your database'. But, on a Unix environment I have the root password and can screw-up much more then the database. But what do I know! Thanks for all of your replies. -----Original Message----- From: Jacob Salomon Sent: Thursday, May 22, 1997 5:49 PM To: informix-list@rmy.emory.edu Subject: Re: Force index usage Tim Kelly wrote: > Ok, how in 7.x can I force an index to be used in an SQL Select > statement? 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. -- -- Jake (In pursuit of undomesticated aquatic avians) +----------------------------------------------------------+ |Aside from that, how did you enjoy the play, Mrs. Lincoln?| +----------------------------------------------------------+