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.