ASA SQL User's Guide
Query Optimization and Execution
How the optimizer works
Optimizer estimates
Column statistics are stored permanently in the database in the system table SYSCOLSTAT. Statistics are automatically updated if a significant amount of data is changed using the INSERT, UPDATE or DELETE statements.
If you suspect that performance is suffering because your statistics inaccurately reflect the current column values, you may want to execute the statements DROP STATISTICS or CREATE STATISTICS. CREATE STATISTICS deletes old statistics and creates new ones, while DROP STATISTICS just deletes the existing statistics.
With more accurate statistics available to it, the optimizer can compute better estimates, thus improving the performance of subsequent queries. However, incorrect estimates are only a problem if they lead to poorly optimized queries.
When you execute LOAD TABLE, statistics are created for the table. However, when rows are inserted, deleted or updated in a table, the statistics are not updated.
For small tables, a histogram does not significantly improve the optimizer's ability to choose an efficient plan. You can specify the minimum table size for which histograms are created. The default is 1000 rows. However, when a CREATE STATISTICS statement is executed, a histogram is created for every table, regardless of the number of rows.
For more information, see MIN_TABLE_SIZE_FOR_HISTOGRAM option [database].
For more information about column statistics, see: