SQL/MX Release 2.0 Best Practices

Updating Statistics 41
Updating Statistics
Statistics are the single most important topic in database management. All tables, even if they are
empty, need to have their statistics generated. Statistics influence almost every aspect of the query plans
to be generated by the optimizer.
You cannot update statistics on system metadata tables, including tables residing in the
DEFINITION_SCHEMA, MXCS_SCHEMA, SYSTEM_DEFAULTS_SCHEMA, and SYSTEM_SCHEMA.
Use the SELECT statement to retrieve the statistics in the SQL/MX HISTOGRAMS table generated by the
UPDATE STATISTICS statement, based on table and column names:
SELECT O.OBJECT_NAME
TABLE_UID,
C.COLUMN_NAME,
H.HISTOGRAM_ID,
H.INTERVAL_COUNT,
H.ROWCOUNT,
H.TOTAL_UEC,
LOW_VALUE,
HIGH_VALUE
FROM
cat.DEFINITION_SCHEMA_VERSION_1200.OBJECTS O,
cat.DEFINITION_SCHEMA_VERSION_1200.COLS C,
cat.sch.HISTOGRAMS H
WHERE O.OBJECT_UID = H.TABLE_UID
AND O.OBJECT_UID = C.OBJECT_UID
AND C.COLUMN_NUMBER = H.COLUMN_NUMBER;
Various statistics about table-data content and size are stored within a SQL/MX database catalog. This
information is used by the SQL/MX database optimizer at query compile time to develop the most
efficient access path to the data. The statistics are produced as a result of running the UPDATE STATISTICS
utility against each table. Normally, statistics are updated after the database has been initially loaded
with data, and thereafter when the volume or range of data changes significantly or after the addition of
secondary indexes.
The SQL/MX database optimizer cannot produce an effective access plan unless the statistics
adequately represent the data within the tables. When no statistics are present, the optimizer assumes
default values, but these values might not produce plans that provide adequate performance. Indexes
might not be used properly without updating statistics on the columns involved in indexes. This can be
done by naming these columns and columns sets (for multi column statistics) explicitly in the update
statistics statement or by using the ON EVERY KEY option of update statistics that would update statistics
on all columns involved in any table indexes including the clustering index of the base table.
You should also generate statistics for all SQL/MX database and ODBC Server catalog tables (and
indexes), because generating statistics improves SQL/MX database query compile time and ODBC Server
performance.