User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 25. Query Optimization
The optimizer bases its selection of access plan on the expected cost of each plan. It uses a mix of metrics to estimate the cost of an access plan:
expected number of rows
use of temporary tables
anticipated amount of CPU and I/O for the access plan
amount of cache utilized
In recognition that disk access is substantially more time-consuming than other operations, Anywhere gives it particular weight.
In keeping with the assumption that Anywhere is to use both disk and memory efficiently, it avoids using temporary tables. To achieve this goal, the optimizer assigns significant cost to plans that use them.
Anywhere bases its estimate of the cost of temporary table on both the row size and the expected number of rows that the table will contain. The optimizer often pessimistically overestimates the actual cost of using a temporary table. When few queries are competing for cache space, the actual cost of a plan with a temporary table can be significantly less than the estimate.
Costing index accessAnywhere calculates a scan factor for each table accessed. For this calculation, it uses both selectivity estimates and the fan-out of the index.
If the index is a key index, then Anywhere assumes that the entries are uniformly distributed in the corresponding table. However, Anywhere assumes that values in the primary-key index are clustered near similar values. This assumption is usually valid. For example, suppose you use a auto-increment column to generate primary-key values. The rows in the table will lie in roughly the same order in the pages of the table as they do in the primary index.
When the rows in a table are arranged on the database pages in the order you wish to read them, less cache space is required because Anywhere can avoid rereading the same pages from disk.