When cardinality statistics go stale

As you saw in When cardinality statistics are automatically updated, cardinality
statistics are automatically updated only in limited cases. Normal insert,
update, and delete statements do not cause the statistics to be updated. This
means that statistics can go stale. Stale statistics can slow your system
down, because they worsen the accuracy of the optimizer's estimates of
selectivity.

Most of the statistics information that
Derby
uses is automatically kept up
to date as part of underlying index and table maintenance.
This information includes the count of rows in the table and
the distribution of data in indexes.

The one piece of information that is not kept up to date is the
average number of duplicates for columns in an index.
This statistic is given a default and
then is updated whenever you create an index or update the statistics by
running either the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS or the
SYSCS_UTIL.SYSCS_COMPRESS_TABLE built-in system procedure.

Statistics are likely to be stale if the number of distinct values in an
index has changed significantly. This can happen often or rarely, depending on
the nature of the column being indexed. You can refresh cardinality statistics
by calling the procedure SYSCS_UTIL.SYSCS_UPDATE_STATISTICS. For information
about this procedure, see the Derby Reference Manual.