Forcing the use of an index

Hi If I have more than one index on a table, how do I force the use of a specific index, on a select statment? Can I do it in the creation of a view? Is there any documentation on this issue that I can look at? David Reed david.reed@compuwin.co.za sending to informix-list

David Reed wrote: > If I have more than one index on a table, how do I force the use of a > specific index, on a select statment? If you are using a version of Informix that supports optimizer directives, you could use something similar to the following example: SELECT {+INDEX(customers cust_index1)} cust_num, cust_name FROM customers WHERE cust_active = "Y"; The specific syntax of the optimizer directive, shown after the SELECT, is just one of several possibilities. The two arguments given are table name and index name. There is also an +AVOID_INDEX optimizer directive that you can use. > Can I do it in the creation of a view? Yes. > Is there any documentation on this issue that I can look at? See the IBM Informix Guide to SQL: Syntax manual. Look up 'Optimizer Directives'. I've just checked the IBM site and the server where the documentation is stored is temporarily down. I was going to try to find out which versions of Informix allow the optimizer directives (I don't recall - would have to look it up), but you'll have to determine that for yourself either by trying it or looking at the proper version of the documentation once the server comes back up. -- June Hunt

June C. Hunt wrote: > David Reed wrote: > >>If I have more than one index on a table, how do I force the use of a >>specific index, on a select statment? > > > If you are using a version of Informix that supports optimizer directives, > you could use something similar to the following example: > > SELECT {+INDEX(customers cust_index1)} > cust_num, cust_name FROM customers > WHERE cust_active = "Y"; > > The specific syntax of the optimizer directive, shown after the SELECT, is > just one of several possibilities. The two arguments given are table name > and index name. There is also an +AVOID_INDEX optimizer directive that you > can use. You're almost always better off leaving the choice of index to the optimiser. The important thing is to have *decent* statistics. -- rh