Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes need to be updated.
Analyze your application workload and create indexes as necessary to improve the performance of the most critical processes.
The Adaptive Server query optimizer uses a probabilistic costing model. It analyzes the costs of possible query plans and chooses the plan that has the lowest estimated cost. Since much of the cost of executing a query consists of disk I/O, creating the correct indexes for your applications means that the optimizer can use indexes to:
Avoid table scans when accessing data
Target specific data pages that contain specific values in a point query
Establish upper and lower bounds for reading data in a range query
Avoid data page access completely, when an index covers a query
Use ordered data to avoid sorts or to favor merge joins over nested-loop joins
In addition, you can create indexes to enforce the uniqueness of data and to randomize the storage location of inserts.
