> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# COLLECT STATISTICS

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](https://docs.kinetica.com/content/config.md#config-main-sql-engine).  To plan an
individual query without using statistics, use the `KI_HINT_NO_STATS`
[hint](https://docs.kinetica.com/content/sql/query/hints.md#sql-hints-global).

The collected statistics can be viewed in the
[ki\_statistics](https://docs.kinetica.com/content/catalogs/kinetica.md#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](https://docs.kinetica.com/content/sql/ddl/drop-statistics.md).

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](https://docs.kinetica.com/content/config.md#config-main-sql-engine).

Collecting statistics requires read
[permission](https://docs.kinetica.com/content/security/sec_concepts.md#security-concepts-permissions) on the entire
*table*; a user whose access to the *table* is limited to certain
[rows](https://docs.kinetica.com/content/sql/security/grant-table-permission.md#sql-security-priv-mgmt-row-grant) or
[columns](https://docs.kinetica.com/content/sql/security/grant-table-permission.md#sql-security-priv-mgmt-col-grant) cannot collect
statistics on it.

```sql title="COLLECT STATISTICS Syntax" theme={null}
COLLECT STATISTICS [ON] [TABLE] [<schema name>.]<table name> COLUMN <column name>
```

## 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*:

```sql COLLECT STATISTICS Example theme={null}
COLLECT STATISTICS ON TABLE example.stats_employee COLUMN dept_id
```

To view the statistics collected on the *table*:

```sql View Statistics Example theme={null}
SELECT column_name, count, unique_vals, num_nulls, min_val, max_val, mode, mode_frequency
FROM ki_catalog.ki_statistics
WHERE schema_name = 'example' AND table_name = 'stats_employee'
ORDER BY column_name
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.