Automatic Calculation of Distribution Statistics

When the CREATE INDEX statement runs successfully, with or without the ONLINE keyword, HCL OneDB™ automatically gathers statistics for the newly created index, and updates the sysdistrib system catalog table with values that are equivalent to an UPDATE STATISTICS operation in a mode that depends on the type of index:
  • Index level statistics, equivalent to the statistics gathered by UPDATE STATISTICS in the LOW mode, are calculated for most types of indexes, including B-tree, Virtual Index Interface, and functional indexes.
  • Column distribution statistics, equivalent to the distribution generated in the HIGH mode, for a non-opaque leading indexed column of an ordinary B-tree index. The resolution percentage is 1.0 if the table has fewer than a million rows, and 0.5 for larger table sizes.

These distribution statistics are available to the query optimizer when it designs query plans for the table on which the new index was created.

For composite key indexes, only distributions of the leading column are created implicitly by the CREATE INDEX statement.

The implicit creation of distribution statistics is not supported for the following types of indexes:
  • Indexes on columns of user-defined data types
  • Indexes on columns of the built-in opaque data types (including BOOLEAN and LVARCHAR)
  • R-tree indexes
  • Attached indexes.

If the calculation of distribution statistics fails during the CREATE INDEX operation, the database server reports that failure in the error log, but continues to create the index.

When distributions are successfully created by an explicit or implicit CREATE INDEX operation, explain information (similar to one generated by UPDATE STATISTICS) such as following is generated if the SET EXPLAIN facility is set to ON. 
Index:          idx_01 on nita.foo
STATISTICS CREATED AUTOMATICALLY:
Column Distribution for:                nita.foo.a
Mode:           MEDIUM
Number of Bins:     101 Bin size:   100.0
Sort data:           0.3 MB
Completed building distribution in:     0 minutes 33 seconds

See the description of the UPDATE STATISTICS statement for information about distribution statistics and about the difference between LOW mode and MEDIUM mode distributions.