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.