RE: Force index usage

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. } } 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