ASA SQL Reference
SQL Statements
Use this statement to erase all optimizer statistics on the specified columns.
DROP STATISTICS [ ON ] [owner.]table-name [ ( column-list ) ]
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.
Must have DBA authority.
Automatic commit.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.