GENERATE EXPRESS STATISTICS

This command is deprecated. The GENERATE STATISTICS and GENERATE EXPRESS STATISTICS commands now conduct the same steps. In environments where the former command behavior is required, Netezza Performance Server Support can restore the previous behavior.

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.

Use the GENERATE EXPRESS STATISTICS command to generate information about a database or table, such as the min/max, null, and estimated dispersion values. The dispersion statistics are not as accurate as the statistics that the system maintains when you run the GENERATE STATISTICS command. They are, however, close approximations.

The GENERATE EXPRESS STATISTICS command produces statistics for only the table or columns you specify.

The system processes full statistics ten columns at a time, whereas it processes express statistics 30 columns at a time.

To estimate how long generating statistics takes divide the number of columns by 10 or 30, which tells you the raw number of passes (full table scans) that the system makes against the data table.

Because full stats attempts to calculate the number of unique values in each column, it is affected by the cardinality in a column and the data types involved.

Because express stats use a hash to estimate the number of unique values in each column, it is less affected by the cardinality of a column.

Syntax

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

Inputs

The GENERATE EXPRESS STATISTICS command takes the following inputs:

Table 1. GENERATE EXPRESS 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 EXPRESS STATISTICS command has the following output:

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

Privileges

You must be the admin user, the table owner, the owner of the database or schema where the table is defined, or your account must have Genstats privilege for thetable or for the Table object class.

Usage

The following provides sample usage.
  • Generate statistics for the table cows: 
    MYDB.SCH1(USER)=> GENERATE EXPRESS STATISTICS ON cows;