optdiag prints the default values for range and in-between selectivity, or the values that have been set for these selectivities in an earlier optdiag session. These values are used for range queries when search arguments are not known when the query is optimized.
For equality search arguments whose value is not known, the total density is used as the default.
Search arguments cannot be known at optimization time for:
Stored procedures that set variables within a procedure
Queries in batches that set variables for search arguments within a batch
Table 2-2 on page 21 in the Performance and Tuning: Optimizer shows the default values that are used. These approximations can result in suboptimal query plans because they either overestimate or underestimate the number of rows to be returned by a query.
See “Updating selectivities with optdiag input mode” for information on using optdiag to supply selectivity values.
