Re: Force index usage

In article <5m4op5$71g@cssun.mathcs.emory.edu>, Bill Ennis <ennis@ssax.com> writes >Well, if you only have a few rows in the table the optimizer actually >made the right choice in doing a table scan. It actually is less work >to just read a few pages sequentially than have to scan a few index pages >and then read a data page. > >One of the pretenses to relational theory and SQL is that you are >allowed to access data without specifying an access path. That is >what the optimizer is for. Now indexes are supposed to be tools that >you provide the database with in order for it to make decisions >regarding the access path. It would be difficult to predict the >fastest access path without knowing the data distribution up front >which usually is not available when you are coding. For the most part >this works well; and only in very specific situations doew there >come a need for what you are asking. > >Now back to your situation. I'd suggest you load the table with your >data amount expected plus a little extra. Then update statistics using >your update statistics scripts that your application will be using. >Then use set explain to review the optimizer index selection. > >-Bill >} >} 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. >} Post the query and table definitions and I'll tell you how to get it to use the required index....Sigh!....why don't people just ask... > >} 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?| >} +----------------------------------------------------------+ >} >} > > >-- >Bill Ennis Voice: 312-474-7516 >SSA Fax: 312-474-7460 >500 W. Madison email: ennis@ssax.com ennis@accesschicago.net -- David Williams