ASA SQL Reference
  SQL Statements

CREATE STATISTICS statement

Description 

This statement should be used only in rare circumstances. It explicitly recreates the statistics that are used by the optimizer.

Syntax 

CREATE STATISTICS table-name [ ( column-list ) ]

Usage 

This statement recreates the statistics that Adaptive Server Anywhere uses to optimize database queries. These statistics analyze the distribution of data in the database for the specified table. The process of running CREATE STATISTICS is time-consuming because it performs ordered scans of the entire table.

In rare circumstances, when your database queries are very variable, and when data distribution is not uniform or the data is changing frequently, you may improve performance by running CREATE STATISTICS against a table or column. This causes an ordered scan of the table or column, using an index if possible.

CREATE STATISTICS overwrites existing statistics. You do not need to drop statistics before executing it.

CREATE STATISTICS creates histograms for the specified table, regardless of the size of the table or the setting of MIN_TABLE_SIZE_FOR_HISTOGRAM.

You can also create statistics using the LOAD TABLE statement.

Permissions 

Must have DBA authority.

Side effects 

Query plans will probably change.

See also 

DROP STATISTICS statement

ALTER DATABASE statement

MIN_TABLE_SIZE_FOR_HISTOGRAM option [database]

LOAD TABLE statement

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.