You can supply index hints when forming a query. Index hints override the optimizer's choice of query access plan by forcing the use of a particular index or indexes. Index hints are typically only used when evaluating the optimizer's choice of plans, and should be used only by advanced users and database administrators. Improper application of index hinting can lead to poor query performance.
You specify index hints using subclauses of the FROM clause. For example, the INDEX clause allows you to specify up to four indexes. The optimizer must be able to use all the specified indexes, otherwise an error is returned.
Specify NO INDEX to disable the use of indexes for the query, and force a sequential scan of the table instead. However, sequential scans are very costly, and take longer to execute. Use this clause only for comparison purposes when evaluating the optimizer's index selection.
By default, if a query can be satisfied using only index data (that is, without having to access rows in the table), the database server performs an index-only retrieval. However, you may want to specify INDEX ONLY ON so that an error is returned in the event that the indexes can no longer be used for index-only retrieval (for example, if they are changed or dropped).
For more information about the index hint clauses you can specify in the FROM clause, see FROM clause.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|