UPDATE STATISTICS

Description

Queries system tables and updates table and column statistics:

• The number of rows in the table (the cardinality)
• The approximate number of occurrences of a value in each column

The optimizer uses the information from UPDATE STATISTICS to calculate a query strategy for a particular c-treeACE SQL statement.

Until a user, application, or c-treeACE SQL script issues an UPDATE STATISTICS statement, the optimizer bases query strategies on values it generates from various defaults. These values will not lead to the best performance, so it is good practice for database administrators to periodically update statistics.

UPDATE STATISTICS only works on tables that have indexes defined on them.

Syntax

UPDATE STATISTICS [ FOR table_name ]

Arguments

table_name

Specifies a single table on which to update statistics. The default is to update statistics on all tables in the database.

Authorization

To issue the UPDATE STATISTICS statement for all tables in the database, the user must have DBA privilege or SELECT privilege on all the tables in the database. To issue the UPDATE STATISTICS statement for a specific table, the user must be the owner or have SELECT privilege on the table.