When cardinality statistics are automatically updated

For the following operations that you perform on a table, Derby automatically creates new statistics or updates existing statistics.

• When you create a new index on an existing non-empty table

  Statistics are automatically created for only the new index.

• When you add a primary key, unique, or foreign key constraint to an existing non-empty table

  If there is no existing index that can be used for the new key or constraint, Derby automatically creates statistics for only the new indexes.

• During query compilation

  If the index cardinality statistics for the tables involved in the query are deemed to be out of date, the query compiler will schedule a job that updates them in a separate thread.

• When you run the SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure

  Statistics are created automatically for all indexes if the statistics do not already exist.

• When you drop a column that is part of a table's index, the statistics for the affected index are dropped. Statistics are automatically updated for the other indexes on the table.

The thread that updates the statistics in the background during query compilation may affect the performance of the user threads. If this causes problems, you can disable automatic updating of statistics by setting the database property derby.storage.indexStats.auto to false.

To diagnose problems with automatic updating of index statistics, an application can set the database property derby.storage.indexStats.log to true. When this property is set, the thread that updates the statistics will write messages to the Derby system log (derby.log) every time it performs a task. You can obtain more detailed logging information by setting the property derby.storage.indexStats.trace, which also lets you specify where the trace output should appear.

See the Derby Reference Manual for details about these properties.

To remove all statistics in case of problems, call the SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure. See the Derby Reference Manual for details about this procedure and about SYSCS_UTIL.SYSCS_COMPRESS_TABLE.