Skip to main content
Collects statistics on a column of a table, for use by the cost-based optimizer when planning queries against the table. The statistics collected for the column are:
  • the number of records in the table
  • the estimated number of distinct non-null values
  • the number of null values
  • the minimum and maximum values
  • the most common values, with their frequencies
When planning a query, the cost-based optimizer uses these statistics to estimate how many records each step of the query will produce, which it uses to choose the order and the strategy for the query’s joins. Statistics affect only how a query is run, not its results. The cost-based optimizer is on by default; see the sql.cost_based_optimization setting in the system configuration. To plan an individual query without using statistics, use the KI_HINT_NO_STATS hint. The collected statistics can be viewed in the ki_statistics catalog table. Statistics are not updated as the table changes; collect them again after significant changes to the table’s data. If the column’s statistics were collected after the table was last modified, the existing statistics are kept and no new collection is done. Statistics are removed when the table is dropped, or with DROP STATISTICS. The database can also collect statistics automatically, in the background, for the columns that the cost-based optimizer finds to be missing statistics; see the sql.automatic_stats settings in the system configuration. Collecting statistics requires read permission on the entire table; a user whose access to the table is limited to certain rows or columns cannot collect statistics on it.
COLLECT STATISTICS Syntax

​
Parameters

<schema name>

Name of the schema containing the table on which to collect statistics

<table name>

Name of the table on which to collect statistics

<column name>

Name of the column on which to collect statistics; only one column can be given per statement

​
Examples

To collect statistics on a column of a table:
COLLECT STATISTICS Example
To view the statistics collected on the table:
View Statistics Example