SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure

The SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure drops all existing cardinality statistics for the index that you specify or for all of the indexes on a table.

You may want to drop the statistics if you are no longer using them or if they become incorrect for some reason. You can call the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS or SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure to recreate them, or you can wait for automatic statistics generation to begin again.

For more information on cardinality statistics, see "Working with cardinality statistics" in the Tuning Derby guide.

Syntax

SYSCS_UTIL.SYSCS_DROP_STATISTICS(IN SCHEMANAME VARCHAR(128), 
                                 IN TABLENAME VARCHAR(128), 
                                 IN INDEXNAME VARCHAR(128))

Note: You can specify null for the INDEXNAME to drop all existing statistics.

Execute privileges

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.

Examples

In the following example, the system procedure drops statistics for the index PAY_DESC on the SAMP.EMPLOYEE table:

CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('SAMP','EMPLOYEE','PAY_DESC');

In the following example, null is specified instead of an index name. For all of the indexes on the EMPLOYEE table in the SAMP schema, the existing statistics are dropped.

CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('SAMP', 'EMPLOYEE', null);