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. uses cardinality statistics to determine
the optimal query plan during the compilation of a query. If the statistics
are missing, 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 guide.
Syntax
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS(IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128),
IN INDEXNAME VARCHAR(128)))
You
can specify null for the INDEXNAME to update
any existing statistics and create statistics for those statistics
that are missing.
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
for more information.
ExamplesIn 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);