How the optimizer determines the number of rows in a table

The optimizer uses a stored row count to determine the number of rows in a table, which is maintained automatically by the system.

Normally, an updated value is stored in the database whenever the database goes through an orderly shutdown (as long as the database is not read-only). Stored row counts become inaccurate if there is a non-orderly shutdown (for example, a power failure or other type of system crash).

You can correct the optimizer's row count without shutting down the system; Derby sets the stored row count for a table to the correct value whenever a query that does a full scan on the base conglomerate finishes. For example, executing the following query sets the row count for table Flights to the correct value:


SELECT * FROM Flights

Derby also sets the stored row count on a table to the correct value whenever a user creates a new index or primary key, unique, or foreign key constraint on the table. This value is not guaranteed to be written to disk.