The SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure updates the cardinality statistics, or creates the statistics if they do not exist, for the index that you specify or for all of the indexes on a table. Derby uses cardinality statistics to determine the optimal query plan during the compilation of a query. If the statistics are missing, Derby might use a query plan which is not the most efficient plan.
Once statistics have been created, they cannot be dropped and should be maintained. It is a good idea to call the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS procedure when the number of distinct values in an index is likely to have changed significantly.
For more information on cardinality statistics, see "Working with cardinality statistics" in the Tuning Derby guide.
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS(IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128),
IN INDEXNAME VARCHAR(128)))
If authentication and SQL authorization are both enabled, all users have execute privileges on this procedure. However, in order for the procedure to run successfully on a given table, the user must be the owner of either the database or the schema in which the table resides. See "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's Guide for more information.
In the following example, the system procedure updates statistics for the index PAY_DESC on the SAMP.EMPLOYEE table:
CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('SAMP','EMPLOYEE','PAY_DESC');
In the following example, null is specified instead of an index name. For all of the indexes, the existing statistics are updated and statistics are created for any missing statistics on the EMPLOYEE table in the SAMP schema.
CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('SAMP', 'EMPLOYEE', null);