Neoview SQL Reference Manual (R2.2)

UPDATE STATISTICS Statement
• “Syntax Description of UPDATE STATISTICS”
• “Considerations for UPDATE STATISTICS”
• “Examples of UPDATE STATISTICS”
The UPDATE STATISTICS statement updates the histogram statistics for one or more groups of
columns within a table. These statistics are used to devise optimized access plans.
In addition to histogram statistics, UPDATE STATISTICS generates physical statistics (index
level, nonempty block count, and EOF) for Neoview SQL tables.
UPDATE STATISTICS is a Neoview SQL extension.
UPDATE STATISTICS FOR TABLE table [CLEAR | on-clause]
on-clause is:
ON column-group-list CLEAR
| ON column-group-list [histogram-option]...
column-group-list is:
column-list [,column-list]...
| EVERY COLUMN [,column-list]...
| EVERY KEY [,column-list]...
| EXISTING COLUMN[S] [,column-list]...
column-list for a single-column group is:
column-name
| (column-name)
| column-name TO column-name
| (column-name) TO (column-name)
| column-name TO (column-name)
| (column-name) TO column-name
column-list for a multicolumn group is:
(column-name, column-name [,column-name]...)
histogram-option is:
GENERATE n INTERVALS
| [SET ROWCOUNT c ]
Syntax Description of UPDATE STATISTICS
table
names the table for which statistics are to be updated. To refer to a table, use the ANSI logical
name.
See “Database Object Names” (page 196).
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 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 Neoview SQL
tables, and SQL returns a warning message. See “Using Statistics” (page 177).
UPDATE STATISTICS Statement 175