ASA SQL User's Guide
Working with Database Objects
Working with indexes
Understanding the Index Consultant
The analysis step is carried out by repeatedly carrying out the following set of operations:
Create a candidate set of virtual indexes.
A virtual index contains no actual data and cannot be used for actual query execution. It can be used by the optimizer when selecting an appropriate execution plan for a query. The Index Consultant generates many alternative sets of virtual indexes.
Optimize the workload operations for this candidate set of virtual indexes.
The Index Consultant retrieves the plan for each statement in the workload, as chosen by the Adaptive Server Anywhere query optimizer. The optimizer considers applicable virtual indexes from the candidate set for each statement. However, the statements are not executed. The Index Consultant does not modify user data.
For each query, the query optimizer compares the execution cost of many alternative execution plans. It estimates the execution cost based on an internal cost model. One important choice that influences the cost of each execution plan is which tables to access using an index, and which to access without using an index. Each set of virtual indexes opens up new execution plan alternatives and closes others.
The cost model depends on the state of the database server. Specifically, although the Index Consultant itself does not read data from disk or execute operations against the database, the cost model depends on which data is in cache and which must be accessed from disk. Therefore, running the Index Consultant may not generate the same recommendations each time you run an analysis on a particular workload. For example, running the Index Consultant on a database server that has just started up (and so has no data in cache) may provide different recommendations to running it on a database server that has been in operation for some time.
For more information about the cost model, see Optimizer estimates.