DROP STATISTICS statement

Syntax

DROP STATISTICS [ ON ] [owner.]object-name [ ( column-list ) ]

object-name : 
table-name 
| materialized-view-name 
| temp-table-name

Remarks

The database server optimizer uses column statistics to determine the best strategy for executing each statement. The database server automatically gathers and updates these statistics. Column statistics are stored permanently in the database in the ISYSCOLSTAT system table. Column statistics gathered while processing one statement are available when searching for efficient ways to execute subsequent statements.

Occasionally, the column statistics can become inaccurate or relevant statistics may be unavailable. This condition is most likely to arise when few queries have been executed since a large amount of data was added, updated, or deleted.

The DROP STATISTICS statement deletes all internal statistical data from the ISYSCOLSTAT system table for the specified columns. This drastic step leaves the optimizer with no access to essential statistical information. Without these statistics, the optimizer can generate inefficient data access plans, causing poor database performance.

The DROP STATISTICS statement requires an exclusive lock on the table against which it is being performed. Execution of the statement cannot proceed until all other connections that refer to the table have either committed or rolled back the referring transactions, or closed any open cursors that refer to the table.

This statement should be used only during problem determination or when reloading data into a database that differs substantially from the original data.

The CREATE STATISTICS and DROP STATISTICS statements do not require or benefit from a commit with regards to the statistics, and only DROP STATISTICS has a commit as a side effect. However, the commit does not save changes to statistics (the statistics governor looks after that). Thus, while DROP STATISTICS does have a side effect of an automatic commit, it commits everything except for the dropping of the statistics.

Privileges

You must be the table owner, or have the MANAGE ANY STATISTICS or DROP ANY OBJECT system privilege.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.