You can use the (index index_name clause in select, update, and delete statements to specify the index to use for a query. You can also force a query to perform a table scan by specifying the table name. The syntax is:
select select_list
from table_name [correlation_name]
(index {index_name | table_name } )
[, table_name ...]
where ...
delete table_name
from table_name [correlation_name]
(index {index_name | table_name }) ...
update table_name set col_name = value
from table_name [correlation_name]
(index {index_name | table_name})...
For example:
select pub_name, title
from publishers p, titles t (index date_type)
where p.pub_id = t.pub_id
and type = "business"
and pubdate > "1/1/93"
Specifying an index in a query may be helpful when you suspect that the query processor is choosing a suboptimal query plan. When you do specify the index:
Always check statistics io for the query to see whether the index you choose requires less I/O than the query processor’s choice.
Test a full range of valid values for the query clauses, especially if you are:
Tuning queries on tables that have skewed data distribution
Performing range queries, since the access methods for these queries are sensitive to the size of the range
Use (index index_name only after testing when you are certain that the query performs better with the specified index option. Once you include an index specification in a query, regularly verify that the resulting plan is still better than other choices made by the query processor.
If a unclustered index has the same name as the table, specifying a table name causes the unclustered index to be used. You can force a table scan using select select_list from tablename (0).
Specifying indexes has these risks:
Changes in the distribution of data could make the forced index less efficient than other choices.
Dropping the index means that all queries and procedures that specify the index print an informational message indicating that the index does not exist. The query is optimized using the best alternative access method.
Increased maintenance, since you must periodically check all queries using this option. Also, each new version of Adaptive Server may eliminate the problems that lead you to incorporate index forcing, so you should check all queries using forced indexes each time you install a new version.
The index must exist at the time the query using it is optimized. You cannot create an index and then use it in a query in the same batch.
Before specifying an index in queries:
Check showplan output for the “Keys are” message to be sure that the index keys are being used as expected.
Use dbcc traceon(3604) or set option show normal to look for other optimization problems.
Run update statistics on the index.
If the index is a composite index, run update statistics on the minor keys in the index, if they are used as search arguments. This can greatly improve query processor cost estimates. Creating statistics for other columns frequently used for search clauses can also improve estimates.
Use set option show_missing_stats on to look for columns that may need statistics.
