Netezza Generate Statistics: A Guide and Best Practices

Netezza uses the cost based optimizer to determine best methods determine redistribution, scan, join, join orders. The optimizer uses statistics information to determine the most efficient way to run a query. System uses the Netezza GENERATE STATISTICS command to generate information about each column proportion of the duplicate values,unique values, NULL values and the maximum and minimum values.

Netezza GENERATE STATISTICS

How Statistics are collected?

Netezza system collects statistics in various ways:

Statistics are automatically generated for certain database operations
Collect full statistics by running Netezza GENERATE STATISTICS command. This operation is required when table changes significantly, say at least 10%
Just In Time Statistics (JIT): Runs at every execution time using sampling and zone maps
You can generate statistics on database or on individual tables. You should maintain the table by running Netezza GENERATE STATISTICS SQL command on regular bases. It usually collects all level of statistics, including dispersion values.

Automatic Netezza Database Statistics

The Netezza system automatically generates two ‘Base Statistics’ (table row count and min/max values for non-character columns) when performing certain database operations:

nzload operations
INSERT operations
UPDATE operations
GROOM TABLE operations
TRUNCATE TABLE operations

Netezza GENERATE STATISTICS Syntax

Syntax of the command:

 GENERATE STATISTICS ON <tablename> [ (<col>[,<col>… ]) ];

Usage:

The following provides sample usage.

Generate statistics for all column of the table TEST:

 TRAINING.ADMIN(ADMIN)=>GENERATE STATISTICS ON TEST;

Generate statistics for all the tables in database TRAINING:

 TRAINING.ADMIN(ADMIN)=>GENERATE STATISTICS;

Netezza GENERATE STATISTICS Best Practices

Following are some of best practices when you are working on generate statistics:

It is very important to collect full statistics by running Netezza GENERATE STATISTICS command when table changes significantly, say at least 10%
You should always collect statistics when perform nzload, insert, update, delete or truncate operations
Generate statistics for columns which are often used. For examples, columns used in JOIN conditions, WHERE clause, GROUP BY clause and ORDER BY clause
You should GENERATE STATISTICS on all TEMP tables those are explicitly used in JOIN
You should not abort the GENERATE STATISTICS command as this will disable the zone maps and rebuilt it. Any interruption leaves the zone maps disabled and this of course impact the performance.

Read:

JUST IN TIME (JIT) Statistics and Usage

Netezza automatically gathers statistics on the table with actual restrictions using sampler scan functionality, Just-In-Time (JIT) for planning.

Read: Netezza Just in time Statistics

JIT Stats is automatically run on all qualified tables, such as:

The table contains more than 5 Million rows
Query contains at least one column restriction
Restrictions do not contain sub-query or sub-plan expressions
Must participate in a JOIN or must have a MATERIALIZED VIEW associated with them
Must be a database table on disk No system tables, EXTERNAL TABLES or virtual tables
JIT Dispersion values calculated on queried columns of tables > 500 Million rows
JIT stats does not eliminate the need to run GENERATE STATISTICS