Indexes require extra space and can slightly reduce the performance of statements that modify the data in the table, such as INSERT, UPDATE, and DELETE statements. However, they can improve search performance dramatically and are highly recommended whenever you search data frequently.
For more information about performance, see Using indexes.
SQL Anywhere automatically indexes primary key and foreign key columns. Thus, manually creating an index on a key column is not necessary and is generally not recommended. If a column is only part of a key, an index can help.
SQL Anywhere automatically uses indexes to improve the performance of any database statement whenever it can. There is no need to explicitly refer to indexes once they are created. Also, the index is updated automatically when rows are deleted, updated, or inserted.
SQL Anywhere lets you supply index hints, which override the optimizer's choice of query access plan by forcing the use of a particular index. This feature should be used only by advanced users and database administrators. It can lead to poor performance.
To supply an index hint, add a FORCE INDEX or WITH INDEX clause to your query. For more information, see FROM clause.
For information on altering database object properties, see Setting properties for database objects.