Statistics
When determining the execution path for a query, the optimizer examines statistics about the data referenced by the query.
The statistics can include the number of rows in the tables, the minimum and maximum values and the number of unique values in interval statistics of columns used in predicates, the existence of primary keys and unique constraints within a table, and the size and configuration of any existing indexes.
Table-level and column-level statistics are stored in the
SYS.TBL_STATS
and SYS.COL_STATS
tables. These
tables are populated when an application calls the ttOptUpdateStats
or
ttOptEstimateStats
built-in procedures, or uses the
ttIsql
statsupdate
or statsestimate
commands.
The optimizer uses the statistics for each table to calculate the selectivity of predicates, such as t1.a=4
, or a combination of predicates, such as t1.a=4 AND t1.b<10
. Selectivity is an estimate of the number of rows in a table. If a predicate selects a small percentage of rows, it is said to have high selectivity, while a predicate that selects a large percentage of rows has low selectivity.