SQL Development

The UPDATE STATISTICS command uses the following syntax:

UPDATE table_name FOR PARTITIONS partition_name, [partition_nam_1, … TABLE INDEX ALL COLUMN STATISTICS AND FOR |

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.

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;

© 2017 Progress Software Corporation and/or one of its subsidiaries or affiliates. All rights reserved.

Progress® OpenEdge® Release 11.7