Contents Index DROP STATEMENT statement [ESQL] DROP SUBSCRIPTION statement [SQL Remote]

ASA SQL Reference
  SQL Statements

DROP STATISTICS statement


Description 

Use this statement to erase all optimizer statistics on the specified columns.

Syntax 

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

Usage 

The Adaptive Server Anywhere optimizer uses statistical information to determine the best strategy for executing each statement. Adaptive Server Anywhere automatically gathers and updates these statistics. These statistics are stored permanently in the database in the system table SYSCOLSTAT. Statistics gathered while processing one statement are available when searching for efficient ways to execute subsequent statements.

Occasionally, the statistics may 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 system table SYSCOLSTAT for the specified columns. This drastic step leaves the optimizer with no access to essential statistical information. Without these statistics, the optimizer may generate very inefficient data access plans, causing poor database performance.

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

Pre-version 8 databases    The DROP STATISTICS syntax has no effect on Adaptive Server Anywhere 7 databases and earlier. To drop statistics on those databases, use the syntax:

DROP OPTIMIZER STATISTICS

This syntax drops all statistics on the database. If you use this syntax on version 8 or later databases, nothing will happen—statistics will not be dropped. This syntax is deprecated.

Permissions 

Must have DBA authority.

Side effects 

Automatic commit.

See also 

CREATE STATISTICS statement

Standards and compatibility 

Contents Index DROP STATEMENT statement [ESQL] DROP SUBSCRIPTION statement [SQL Remote]