GENERATE STATISTICS

Use the GENERATE STATISTICS command to generate information about each column proportion of duplicate values, and the maximum and minimum values. The optimizer uses this information to determine the most efficient way to run a query. Run the GENERATE STATISTICS command when you initially load a table and after the table data is changed (for example, after you add or delete records). This enables the query optimizer to make better choices when planning user queries.

Restriction: You cannot run the GENERATE STATISTICS command inside a transaction block (BEGIN/COMMIT pair).
Note: As of release 4.6, the GENERATE EXPRESS STATISTICS command is deprecated and replaced by the GENERATE STATISTICS command.

Syntax

Syntax the GENERATE STATISTICS command:
GENERATE STATISTICS ON <tablename> [ (<col>[,<col>… ]) ];

Inputs

The GENERATE STATISTICS command takes the following inputs:

Table 1. GENERATE STATISTICS inputs
Input Description
<tablename> The name of a table to analyze. The default is all tables in all schemas of the current database.
<col> The column or columns to analyze.

Output

The GENERATE STATISTICS command has the following output:

Table 2. GENERATE STATISTICS output
Output Description
GENERATE STATISTICS The command completed successfully.

Privileges

You must be the admin user, the table owner, the owner of the database or schema that contains the table, or your account must have the Genstats privilege for the table or for the Table object class. In addition, your user account must have the List privilege for the schema that contains the table.

Usage

The following provides sample usage.
  • Generate statistics for column cnumber of the table cows: 
    MYDB.SCH1(USER)=> GENERATE STATISTICS ON cows (cnumber);
  • Generate statistics for all the tables in database mydb: 
    MYDB(USER)=> GENERATE STATISTICS;