Update Statistics

Go Up to Available object actions by DBMS

The Update Statistics dialog lets you update the statistics for an active table or index. As indexes grow and shrink in response to data modification, the accuracy of their statistics can deteriorate.

The following topics provide details on updating statistics by supported DBMS and object type:

• Updating statistics for tables or indexes (IBM DB2 for Linux, Unix, and Windows version 8.2.2)
• Updating statistics for views (IBM DB2 for Windows, Unix, and Linux)
• Updating statistics for databases, indexes, or tables (Microsoft SQL Server)
• Updating statistics for indexes or tables (Sybase ASE)

Updating statistics for tables or indexes (IBM DB2 for Linux, Unix, and Windows version 8.2.2)

To Update Statistics for an Index or Table

1. Initiate an Update Statistics action against one or more tables or indexes. For more information see Initiating an object operation.

   The Update Statistics dialog opens.

2. Use the following table as a guide to understanding and modifying the settings in this dialog.

Tab	Options	Description
Table Options	Update table statistics	Updates table statistics.
	Distribution Options	Do not collect column statistics - Column statistics provide information that the optimizer uses to choose the best access plans for queries. Collect column statistics on key columns only - Collects column statistics on columns that make up all the indexes defined on the table. Collect column statistics on all columns - Collects column statistics for all columns. Column statistics provide information that the optimizer uses to choose the best access plans for queries. Frequency - Lets you specify the maximum number of frequency values to collect, between 1 and 32767. Quantiles - Lets you specify the maximum number of distribution quantile values to collect, between 1 and 32767.
	Column Options	Do not collect distribution statistics - Does not collect basic statistics or distribution statistics on the columns. Collect distribution statistics on key columns only - Collects both basic statistics and distribution statistics on key columns only. Collect distribution statistics on all columns - Collects both basic statistics and distribution statistics on all columns. For efficiency both of RUNSTATS and subsequent query-plan analysis, you might collect distribution statistics on only the table columns that queries use in WHERE, GROUP BY, and similar clauses. You might also collect cardinality statistics on combined groups of columns. The optimizer uses such information to detect column correlation when it estimates selectivity for queries that reference the columns in the group. Exclude XML columns - Lets you collect statistics on non-XML columns only. XML columns are not included in statistics collection.
Index Options	Update index statistics	Lets you enable and disable statistics updates for indexes and controls the following settings:
	Collect extended index statistics	Collects extended index statistics, the CLUSTERFACTOR and PAGE_FETCH_PAIRS statistics that are gathered for relatively large indexes.
	Collect sample statistics	A CPU sampling technique is used when compiling the extended index statistics. If the option is not specified, every entry in the index is examined to compute the extended index statistics.
Access Options	Allow read only access during collection	Allows read only access while the statistics are being updated.
	Allow read/write access during collection	Allows read and write access while the statistics are being updated.

3. Click Execute. For information on the Preview option, see Preview.

Updating statistics for views (IBM DB2 for Windows, Unix, and Linux)

For IBM DB2 for Windows, Unix, and Linux, you can update the statistics for a view.

To Update Statistics for a View

1. Initiate an Update Statistics action against one or more views. For more information see Initiating an object operation.
2. Use the following table as a guide to choosing options:

Pane	Options
Action Options	Lets you work with the following settings: Column Statistics- lets you enable or disable collection of Column Statistics update, Distribution Statistics, Frequency, and Quantiles - let you enable and disable collection of distribution statistics and provide num_freqvalues and num_quantiles RUNSTATS options Allow Write Access - lets you enable or disable write access during statistics collection.
Dependencies	Lets you view referencing or referenced objects. For more information, see Dependencies.
Preview	Lets you view the DDL generated for the operation. For more information, see Preview.

3. Schedule or Execute the statistics update.

Updating statistics for databases, indexes, or tables (Microsoft SQL Server)

You can update statistics so that Microsoft SQL Server performs the most efficient query possible. This feature updates statistical information on your database so that the query processor can determine the optimal strategy for evaluating a query. These statistics record the key values used for distribution in an database.

You can use the Update Statistics dialog if there is significant change in the key values in the database, if a large amount of data in an indexed column has been added, changed, or removed, or if a table has been truncated causing significant changes in the distribution of key values.

The Update Statistics dialog lets you specify tables and indexes for validation. This dialog box offers different update options depending on your version of Microsoft SQL Server.

Tip: Avoid updating statistics on your target tables during busy access periods. Microsoft SQL Server locks remote tables and indexes while reading data for update statistics.

For Microsoft SQL Server version 7 or later, the Update Statistics dialog lets you specify a full or a percentage of a full scan to be used for updating table or index statistics. It also lets you enable or disable future automatic recomputations of statistics. These recomputations are made at Microsoft SQL Server’s discretion. When updating statistics for tables, this dialog box also lets you specify the type of statistics you require.

To Update Statistics for a Database, Index, or Table

1. Initiate an Update Statistics action against one or more databases, indexes, or tables. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying the settings in the Update Statistics dialog:

Step	Settings and tasks
Action Options	Scan Range (tables and indexes only)	Full Scan - Select when you want index statistics on every available row. Sample Scan - Select when database size prohibits a full scan and you can afford to rely on statistics extrapolated from a sample of all available rows.
	Sample Count (tables and indexes only)	If you specified a Scan Range of Sample Scan, provide a count.
	Sample Unit (tables and indexes only)	If you specified a Scan Range of Sample Scan, specify either % or Rows.
	Statistics Type (tables only)	Index - Select if you only require statistics on the target tables’ indexed columns. Columns - Select if you require statistics on the target tables in their entirety. All existing statistics - Select if you require statistics on the whole database.
	Statistics Recompute (tables and indexes only)	Select if you want Microsoft SQL Server to recompute and update the statistics for the index as part of its normal internal maintenance. Deselect if you want the scheduling of future recomputations to be solely your responsibility.
Dependencies (tables and indexes only)	Lets you view referencing or referenced objects.
Preview	Lets you view the DDL generated for the operation. For more information, see Preview.

3. Use the Execute or Schedule button to perform the operation.

Updating statistics for indexes or tables (Sybase ASE)

The Update Statistics dialog lets you specify tables and indexes for validation. This dialog box offers different update options depending on your version of Sybase ASE.

Tip: Avoid updating statistics on your target tables during busy access periods. Sybase ASE locks remote tables and indexes while reading data for update statistics.

To Update Statistics for a Database, Index, or Table

1. Initiate an Update Statistics action against one or more tables or indexes. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying the settings in the Update Statistics dialog:

Step	Settings and tasks
Action Options	Index (tables only)	Enabling this option updates statistics for indexes of the table.
	Table (tables only)	Enabling this option updates table-specific statistics. Column statistics stored in sysstatistiics are not affected.
	Partition Name (available when a single index or table is selected)	The name of the partition to be updated.
	Step Values	Lets you specify the number of histogram steps.
	Consumers	Specifies the number of consumer processes to be used for a sort when a list of columns is provided and parallel query processing is enabled.
	Sampling Percent	Specifies the percentage of the column to be randomly sampled in order to gather statistics.
Columns	(available only when a single table is selected)	Lets you specify all columns or specific columns to be used for the operation.
Dependencies	Lists referring and referenced objects potentially impacted by the change. For details, see Dependencies.
Preview	Lets you view the DDL generated for the operation. For more information, see Preview.

3. Use the Execute or Schedule button to perform the operation.