Query plans consist of retrieval tactics and an ordered set of execution steps, which retrieve the data needed by the query. In developing query plans, the query optimizer examines:
The size of each table in the query, both in rows and data pages, and the number of OAM and allocation pages to be read.
The indexes that exist on the tables and columns used in the query, the type of index, and the height, number of leaf pages, and cluster ratios for each index.
The index coverage of the query; that is, whether the query can be satisfied by retrieving data from the index leaf pages without accessing the data pages. Adaptive Server can use indexes that cover queries, even if no where clauses are included in the query.
The density and distribution of keys in the indexes.
The size of the available data cache or caches, the size of I/O supported by the caches, and the cache strategy to be used.
The cost of physical and logical reads; that is, reads of physical I/O pages from the disk, and of logical I/O reads from main memory.
join clauses, with the best join order and join type, considering the costs and number of scans required for each join and the usefulness of indexes in limiting the I/O.
Whether building a worktable (an internal, temporary table) with an index on the join columns is faster than repeated table scans if there are no useful indexes for the inner table in a join.
Whether the query contains a max or min aggregate that can use an index to find the value without scanning the table.
Whether data or index pages must be used repeatedly, to satisfy a query such as a join, or whether a fetch-and-discard strategy can be employed because the pages need to be scanned only once.
For each plan, the query optimizer determines the total cost by computing the costs of logical and physical I/Os, and CPU processing. If there are proxy tables, additional network related costs are evaluated as well. The query optimizer then selects the cheapest plan.
The query processor for Adaptive Server versions 15.0.2 and later defers the optimization of statements in a stored procedure until it executes the statement. This benefits the query processor because the values for local variables are available for optimization for their respective statements.
Earlier versions of Adaptive Server used default guesses for selectivity estimates on predicates using local variables.
