SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-246
UPDATE STATISTICS Statement
CLEAR
deletes some or all histograms for the table table. Use this option when new
applications no longer use certain histogram statistics.
If you do not specify column-group-list, all histograms for table are deleted.
If you specify column-group-list, only columns in the group list are deleted.
ON column-group-list
specifies one or more groups of columns, column-group-list, for which to
generate histogram statistics with the option of clearing the histogram statistics.
You must use the ON clause to generate statistics stored in histogram tables. If
you omit it, physical statistics are generated for SQL/MX tables, and NonStop
SQL/MX returns a warning message. See Using Statistics on page 2-249.
column-list
| EVERY COLUMN [, column-list]
| EVERY KEY [, column-list]
specifies the ways in which column-group-list can be defined. The
column list represents both a single-column group and a multicolumn group.
Single-column group:
column-name | (column-name)
| column-name TO column-name
| (column-name) TO (column-name)
are the ways you can specify individual columns or a group of individual
columns.
To generate statistics for individual columns, list each column. You have
the option of listing each single column name within or without
parentheses.
Multicolumn group:
(column-name, column-name [,column-name]...)
specifies a multicolumn group.
To generate multicolumn statistics, group a set of columns within
parentheses, as shown. You cannot specify the name of a column more
than once in the same group of columns.
One histogram is generated for each unique column group. Note that duplicate
groups are ignored and processing continues. When you run UPDATE
STATISTICS again for the same user table, the new data for that table replaces
the data previously generated and stored in the table’s histogram tables.
Histograms of column groups not specified in the ON clause remain
unchanged in histogram tables.