Improving Performance of UPDATE STATISTICS

When you execute the UPDATE STATISTICS statement, the database server uses memory and disk to sort and construct data distributions. You can affect the amount of memory and disk available for UPDATE STATISTICS with the following methods:

• PDQ priority

  Although the UPDATE STATISTICS statement is not processed in parallel, you can obtain more memory for sorting when you set PDQ priority greater than 0. The default value for PDQ priority is 0. To set PDQ priority, use either the PDQPRIORITY environment variable or the SQL statement SET PDQPRIORITY.

  For more information on PDQ priority, see Allocating Resources for Parallel Database Queries.

• DBUPSPACE environment variable

  You can use the DBUPSPACE environment variable to constrain the amount of system disk space that the UPDATE STATISTICS statement can use to construct multiple column distributions simultaneously.

  For more information on this environment variable, see the IBM Informix: Guide to SQL Reference.