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.