RE: Force index usage

In article <5m4kmk$1c3@cssun.mathcs.emory.edu>, Tkelly@svhs.org (Tim Kelly) wrote: > >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. > > Hi Tim, We've recently moved from SE5 to OL7. We did have 'Index selection' problems in SE5 and had modified some of our 4gls to 'force' specific index usage. These problems do not seem to exist in OL7 (though UPDATE STATISTICS MEDIUM on all columns and HIGH on left-most index columns would be a neccessity - possibly OPTCOMPIND=0 too). Our solution, probably of academic interest only now, went something like this. Background: A Reporting 4GL could be used for multiple purposes. The selection criteria, therefore, allowed constraints on many columns of the table(s). Depending on what the user wanted from the database, s/he would constrain specific columns and leave the others 'open'. Solution : Our 4gl program would examine the constraints entered by the user on the screen, and using some very basic rules, conclude which of the competing indexes was the best. The Select stmt used to retrieve information would then be constructed without WHERE clauses on columns whose indexes we did not want to use. We would PREPARE the select char variable, DECLARE the cursor and then OPEN it using only the required user-entered constraints. Within the FOREACH loop, we would discard unwanted rows. Its surprisingly simple syntax once you get the hang of it and we used it quite effectively in quite a few reporting 4gls where the user requirements and index choices were very clear. We have not rewritten these programs to leave the thinking to the OL7 optimizer, but new 4gls we write do not bother with this sort of stuff anymore. BTW, 'force index' is NOT on my wish list (Please, no salvos) Bye, ---------------------- Rudy Fernandes GIC, Kuwait OL 7.20, 4Gl 6.04 ----------------------