SYBASE: update statistics commands

The update statistics commands create statistics, if there are no statistics for a particular column, or replaces existing statistics if they already exist. The statistics are stored in the system tables systabstats and sysstatistics. The syntax is:

update statistics table_name 

    [ [index_name] | [( column_list ) ] ]

    [using step values ]

    [with consumers = consumers ]

update index statistics table_name [index_name] 

    [using step values ]

    [with consumers = consumers ]

update all statistics table_name 

The effects of the commands and their parameters are:

·         For update statistics:

o    table_name – Generates statistics for the leading column in each index on the table.

o    table_name index_name – Generates statistics for all columns of the index.

o    table_name (column_name) – Generates statistics for only this column.

o    table_name (column_name, column_name...) – Generates a histogram for the leading column in the set, and multi column density values for the prefix subsets.

o    using step values – Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.

·         For update index statistics:

o    table_name – Generates statistics for all columns in all indexes on the table.

o    table_name index_name – Generates statistics for all columns in this index.

·         For update all statistics:

o    table_name – Generates statistics for all columns of a table.

o    using step values – Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.

NOTE: Use update statistics on huge tables before starting any bulk data actions on that table. It is one kind of performance tuning as it refreshes indexes so that it will get out of load which could be due to all recent transactions