Updating Statistics for Tables

Although a change to the database might make information in the systables, syscolumns, sysindices, sysfragments, sysdistrib, and sysfragdist system catalog tables obsolete, the database server does not automatically update those tables after most SQL statements.

Issue an appropriate UPDATE STATISTICS statement in the following situations to ensure that the column distribution information in the system catalog tables reflects the current state of the database:

  • You perform extensive modifications to a table.
  • An application changes the distribution of column values.

    The UPDATE STATISTICS statement refreshes the data distribution statistics that the database server uses to optimize queries on the modified objects.

  • You upgrade a database for use with a newer database server.

    The UPDATE STATISTICS statement converts the old indexes to conform to the newer database server index format and implicitly drops the old indexes.

    You can convert the indexes table by table or for the entire database at one time. Follow the conversion guidelines in the HCL OneDB™ Migration Guide.

If your application makes many modifications to the data in a particular table, update the system catalog for that table routinely with UPDATE STATISTICS to improve query efficiency. The term many modifications is relative to the resolution of the distributions. If the data modifications have little effect on the distribution of column values, you do not need to execute UPDATE STATISTICS.

Distribution Statistics in NLSCASE INSENSITIVE databases

In a database created with the NLSCASE INSENSITIVE property, database server operations on columns and expressions of NCHAR or NVARCHAR data types make no distinction between upper case and lower case letters. In data sets that include strings of the same sequence of letters but with case variants, generating the data distributions of NCHAR and NVARCHAR columns requires fewer bins than in a case-sensitive database containing the same records. The database server identifies all the case-variant values as only a single distinct value, and uses this result when it generates the column, index, or fragment-level statistics.

For more information about NLSCASE INSENSITIVE databases, see Duplicate rows in NLSCASE INSENSITIVE databases, Specifying NLSCASE case sensitivity, and NCHAR and NVARCHAR expressions in case-insensitive databases.