DROP STATISTICS
Valid in: SQL
The DROP STATISTICS statement deletes histograms created by CREATE STATISTICS.
This statement has the following format:
DROP {STATISTICS | STATS} FOR table [(column list)] [{, table [(column list)]} [WITH opt [,opt]]
FOR table (column list)
Specifies the table and optionally columns to drop statistics for. The following can be specified instead of a table and column list:
FOR ALL TABLES
Drops statistics for all tables in a database.
FOR user TABLES
Drops statistics for all tables owned by the specified user ID.
WITH opt [,opt]
Drops COLUMN COMPARE or COLUMN GROUP statistics (which are created using the same options on CREATE STATISTICS), or both, in addition to dropping the column's value histogram statistics. Values for opt are:
COLUMN COMPARE
Drops column comparison statistics
COLUMN GROUP
Drops column group statistics
If no WITH-option is given, no column comparison or column group statistics are dropped.
If one WITH-option is given, all specified statistics that involve any of the columns listed in the DROP column list are dropped.
If no DROP column list is given, the implication is all columns, so all specified statistics for the table are dropped.
To drop all statistics for a table—value histogram, column comparison, and column group statistics—use WITH COLUMN COMPARE, COLUMN GROUP.