» Documentation » SQL Reference

Generate Statistics

Syntax

{GENERATE | GEN} {STATISTICS | STATS} FOR
   {TABLE table_path
    | DATABASE database_name
    | ALL}
   [IF MODIFIED percentage_modified]
   [WITH STEP percentage_resolution] 

Description

GENERATE STATISTICS will scan tables, updating the system tables systables and syscolumns with statistical information. These statistics are used by the query optimizer. Generate statistics will also regenerate the DQL plans of all relavent queries.

If TABLE is specified statistics are generated for table_path, where table_path is of the form database_name.table_name or table_name, if it is in the currently USEd database. If DATABASE is specified, statistics are generated for all tables in database_name. Otherwise, if ALL is specified, all tables in all databases in the cluster will have their statistics regenerated.

GENERATE STATISTICS will only regenerate new statistics if the amount of rows inserted/deleted/updated since the last GENERATE STATISTICS was run on the table is greater than percentage_modified percent. This is, by default, 10%, when IF MODIFIED is not specified.

GENERATE STATISTICS will not, by default, scan every row, but rather take a random sample of data to base its results on. When WITH STEP is specified, percentage_resolution is the percentage of data used to generate the statistics, otherwise, 10% of the data is used. If 100 is specified, the statistics would be wholly accurate, however this would take a long time.