As described in What are cardinality
statistics?, the cardinality statistics for a table influence the
optimizer's choice of a query plan for a query which accesses the table's data.
If the cardinality statistics are missing or out of date, the optimizer may
choose an inferior query plan, resulting in poor performance.
One common situation which can causing missing or out of date statistics is
when indexes are created before the data is added to the tables. Cardinality
statistics are automatically updated in certain situations, such as when an
index is added to an existing non-empty table.
When cardinality statistics are automatically
updated describes the automatic statistics updates in more detail.
However, adding, updating, and deleting data after the index has been created
can cause the cardinality statistics to become stale; see
When cardinality statistics go stale
for more information about what can cause missing or out of date statistics.
To ensure that the statistics are available and accurate, you can run either
of the following built-in system procedures:
- SYSCS_UTIL.SYSCS_COMPRESS_TABLE
- SYSCS_UTIL.SYSCS_UPDATE_STATISTICS
Note that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system
procedure does not update statistics as part of its processing.
You can find additional information about these system procedures, including
the syntax for invoking them, in the
.