Re: [Q] How do I force usage of index

Daniel Williams <#danny@uk.ibm.com> wrote in article <341014AA.41C6@uk.ibm.com>... > > Is there any way I can force usage of the indexes to see if it makes any > > difference (presuming that the the query optimizer has decided not to > > use them for some reason) ? > > Have you run UPDATE STATISTICS (see SQL manuals for more details). If you don't do this, the > query optimiser has no idea how to really access the data. You can also issue the statement > ALTER INDEX ixname TO CLUSER; to place your data in accordance with theindex. True on the UPDATE STATISTICS - without doing it at least once, you will always get sequential scans of tables. Start with stats low (i.e. UPDATE STATISTICS LOW) and work up if set explain does not show the behavior you desire. And in answer to the question in the subject, there is NO way to force an index to be used. The best you can do is make it the most desirable choice by ref'ing the indexed column in the query (i.e. where indexed_column = "some value"), but there are cases where even this will result in a sequential scan. As per the clustered index, be aware that this is a static clustering, i.e. any rows added after the cluster has been performed will not necessarily be in clustered order (they only would be if the column value happens to be sequentially higher than the current highest value). In my experience, clustered indexes are useful only in some special circumstances. For a table with high churn, they are a waste (IMO). > Also, look at the parameter OPTCOMPIND (see admin guide for more details). This influences the > access path chosen by the query optimiser. This includes whether each path is evaluated or only > the step that appears to be the best at each level. This is inaccurate. OPTCOMPIND determines whether index joins are to be preferred over hash joins. What you are after is SET OPTIMIZATION HIGH|LOW, which can only be set via an SQL statement; there is no parameter to control this. It is only useful if you have so many indexes that the query is spending a lot of time in optimization. Dave