Try OpenEdge Now
skip to main content
SQL Development
Optimizing Query Performance : Affecting what the optimizer produces : Updating index statistics
 

Updating index statistics

The UPDATE STATISTICS command uses the following syntax:
UPDATE ([ TABLE | INDEX |[ ALL ] COLUMN ] STATISTICS [ AND ])...[ FOR table_name[FOR PARTITIONS partition_name, [partition_nam_1, … ]]
The following example demonstrates the use of the UPDATE STATISTICS statement for a single table:
UPDATE INDEX STATISTICS FOR Employee;
The following example updates statistics for indexes and columns for a single employee:
UPDATE TABLE STATISTICS AND INDEX STATISTICS
AND COLUMN STATISTICS FOR Employee ;
The following example updates index statistics of partitions USA_Customer, EUROPE_Customer and ASIA_Customer for table Customer by using FOR PARTITIONS which updates only the local index statistics and not the global index statistics:
To create the new index statistics for all tables in a database, simply use the statement shown in the following example.
Table 24. UPDATE INDEX STATISTICS statement
UPDATE INDEX STATISTICS ;
Notes
*To create the new index statistics, SQL makes one pass over each index, reading every index entry and counting unique values. This is usually a CPU-intensive operation. When a table has many indexes, this operation can take quite a bit more time than the default UPDATE STATISTICS.
*UPDATE STATISTICS does not lock user data. It only locks the output statistics rows (and also acquires a shared lock on the schema). This means that user-level transactions can freely run concurrently with UPDATE STATISTICS.
*To get the best SQL query performance, or if a SQL performance problem occurs, be sure that the database has a full set of SQL statistics. To get a full set of SQL statistics, execute this SQL statement:
*UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS;
*At a slightly longer execution time, you can get even better SQL statistics by executing:
*UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS;