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
Derby Reference Manual.
