Updating the Statistics Information 

You can use the Database Manager GUI to update the SQL Optimizer statistics. Additionally, you can also automate this process.

Prerequisites

The database instance is in the ONLINE operational state.

Updating Statistics Information Manually

...

       1.      Select the required database instance in the list of database instances. Choose Instance ® Tuning ® Optimizer Statistics.

       2.      Create a list of the tables for which the statistics are to be updated:

○     To select all the tables for which an update is necessary, apply the default value * under Search.

○     To select certain tables for which an update is necessary, enter the corresponding search arguments under Search.

Search Argument	Description
Owner	Owner
Table Name	Table name
Column Name	Name of column

○     To select all tables for which an update of statistics is possible irrespective of whether or not they need to be updated, under Advanced choose the option Select from tables.

       3.      Choose Actions ® Search.

A list of the tables is displayed that matches your criteria under Search and Advanced. From the Update Statistics Date  and Update Statistics Time  columns, you can tell when the statistics for this table were last updated.

       4.      From the list of tables, choose the tables for which you want to update the statistics.

       5.      You have the following options when updating the statistics:

○     Updating the statistics for all rows of the selected tables (system default)

○     Updating the statistics for a sample

To define the sample, enter the corresponding parameter under Estimate .

Parameter	Description
Use estimate value	Defines whether a sample is to be used
Percent	Percentage of table rows to be checked

The reliability of the random sample depends on the size of the table and the physical location of the data. For large tables (more than 1 million entries), we recommend a sample size of 20%.

To exclude a table from the updating of statistics, you select the value 0 for the sample size for this table.

○     If you want to update the column statistics in addition to the table statistics, choose the option Include update columns for marked tables under Advanced, double-click the desired table in the list and then choose the desired columns.

       6.      Choose Actions ® Execute.

Switching Automatic Update of Statistics Information On-/Off

You can switch on/off automatic update of statistics information automatically when creating a database instance. To do this, proceed as described in Adding a Database Instance: Custom..

If you want to switch this option on/off at a later date, use the following Database Manager GUI function:

...

       1.      Select the required database instance in the list of database instances. Choose Instance ® Automatic Statistics Update.

       2.      Choose On or Off.

See also:

Concepts of the Database System, SQL Optimizer Statistics