Ensure that table statistics are accurate

Ensure that table statistics are accurate, since missing or out-of-data statistics can result in poor query plan selection.

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:

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.

Related concepts
Use prepared statements with substitution parameters
Create indexes, and make sure they are being used
Increase the size of the data page cache
Tune the size of database pages
Avoid expensive queries
Use the appropriate getXXX and setXXX methods for the type
Tune database booting/class loading
Avoid inserts in autocommit mode if possible
Improve the performance of table functions
Configure Derby to use an in-memory database
Increase the concurrency of sequences
Shut down the system properly
Put Derby first in your classpath