Optimizer Statistics

The query optimizer uses statistics to provide it with data about the contents of the tables in the database. This data helps the optimizer pick the best query execution plans.

Table Statistics (Stats v3)

Stats v3 is the third generation of improved query optimizer statistics. It introduces enhanced statistics that improve query optimization for specific query types.

Stats v3 will generate all the index statistics that were generated by Stats v2:

  • Table cardinality

  • Index prefix scalar statistics:

    • Number of distinct values

    • Null count

  • Histograms, which provide data about the distribution of values in a column or across multiple columns in an index.

In addition, Stats v3 will generate the following statistics:

  • Per Column scalar statistics on all table columns (with the exception of LOB type columns)

    • Number of distinct values

    • Null count

  • Histograms, for all index prefix columns.

  • Histograms for any columns or column groups, and their prefixes, requested using CREATE STATISTICS.

    The maximum key size for histograms is 2048 bytes. To guarantee this maximum, it is not possible create statistics on columns with unbounded size (string, bytes, blob, clob). To create statistics on such column groups, change the column data type to a fixed size data type. For example, varchar or binary.

  • Scalar statistics for any column groups requested using CREATE STATISTICS.

Advantages provided by Stats v3 are:

  • It generates optimizer statistics for each table, not only indexes, allowing the optimizer to utilize statistics on non-indexed columns.

  • It is not affected by scalability issues that negatively impact index statistics on large tables.

How to generate Table Statistics

Stats v3 statistics are generated in one of two ways:

  1. User runs the ANALYZE command

  2. The system detects a significant change has occurred to a table and automatically requests the regeneration of statistics

How auto-update works

Unlike index statistics Stats v2, Stats v3 does not keep statistics permanently up to date after every change to the index. Stats v3 regenerates the statistics automatically once enough changes have occurred to the table. A change is an insert, update, delete, or replace operation on the table.

Auto-update uses a background job to execute ANALYZE automatically to update the statistics. The extent of the change to the table will determine which statistics require regeneration.

To view the tables that are currently queued to update statistics automatically, use:

SELECT * FROM SYSTEM.LOCALSTATSUPDATEJOBS;

For more information, see LOCALSTATSUPDATEJOBS system table.

Auto-update can be disabled by setting the disable-stats-v3-auto-update database option to true on all NuoDB transaction engines. When disabled, the application must keep statistics up to date by running ANALYZE.

The following SQL table functions can be used to see the current information about the statistics that have been collected for a table.

How to enable Table Statistics

Table Statistics (Stats v3) are enabled by default, no additional configuration is required to enable them.

To disable Stats v3 and revert to Stats v2, follow the instructions in How to enable index statistics.

How to configure Table Statistics

To configure the frequency at which the statistics get updated automatically, use these SQL System Properties:

  • TABLE_CHG_TOLERANCE

  • TABLE_CHG_TOLERANCE_SCALAR_STATS_REFRESH_REQUIRED

Creating additional statistics

By default, Stats v3 will generate scalar statistics on all single columns (with the exception of LOB types), and histograms and scalar statistics on all combinations of columns covered by an index. To create these statistics on a combination of columns without creating an index, use CREATE STATISTICS s1 ON t1(c1, c2, c3). For more information, see CREATE STATISTICS.

Example

Example 1

Create histogram and scalar statistics on groups of columns using CREATE STATISTICS.

CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
CREATE STATISTICS stat2 ON t1 (f1, f2);