Often, SQL Anywhere can evaluate search conditions with the aid of indexes. Using indexes speeds optimizer access to data and reduces the amount of information read and processed from base tables. For example, if a query contains a search condition WHERE column-name=value, and an index exists on the column, an index scan can be used to read only those rows of the table that satisfy the search condition.

Indexes also improve performance dramatically when joining tables.

Whenever possible, the optimizer attempts index-only retrieval to satisfy a query. With index-only retrieval, the database server uses only the data in the indexes to satisfy the query, and does not need to access rows in the table.

In the case where there are no indexes for the optimizer to use, a sequential table scan is performed instead, which can be expensive.

The optimizer automatically chooses to use the indexes it determines will lead to the best performance. However, you can also use index hints in your query to specify the indexes you want the optimizer to use. If any of the specified indexes cannot be used, an error is returned. Note that index hinting can result in poor performance and should only be attempted by experienced users. See FROM clause.

Use the Index Consultant to determine whether additional indexes are recommended for your database. See Index Consultant.

See also

• Using predicates in queries