The update statistics command gathers information about the distribution of key values in specified indexes, for all columns in an index, table, or partition. ‘Statistics’ help the ASE optimizer make decisions about which indexes to use during query processing. Typically, We have to use update statistics when data profile of a table changes.

*

update statistics updates/creates table statistics stored in systabstats and sysstatistics.If you run the command for a specific data partition, statistics are generated and updated only for that data partition and for any local index partitions. If you run update statistics for a specific index partition, only the statistics for that index partition are updated.*** Permission to issue the update statistics command defaults to the table owner.

Sybase Update Statistics

update statistics table_name [index_name] with sampling = N percentUpdates statistics on the leading columns of all indexes on the specified table or partition or the leading column of a specified index.

Syntaxupdate statistics table_name

[[ partition data_partition_name ] [ (column_list ) ] |

index_name [ partition index_partition_name ] ]

[ using step values ][ with consumers = consumers ][, sampling=N

percent ]

Sybase Update Statistics

update index statistics Updates statistics on all columns of all indexes on the table or of the specified index. If sampling is specified, it is used to gather statistics on the non-leading index columns. Why is this important ?

Syntax:update index statistics table_name [[ partition data_partition_name ] | [ index_name [ partition index_partition_name ] ] ] [ using step values ] [ with consumers = consumers ] [, sampling=N percent]

Sybase Update Statistics

Non-leading columns are read into a worktable & sorted for updating statistics. The additional work can be reduced when sampling is used, the sampled values are still read into a worktable and sorted,, but since the sampled set of values is considerably smaller, the process takes much less time.

Updating partition statisticsThis command generates distribution information for a particular partition in a table.

Syntaxupdate all statistics table_name [ partition data_partition_name ]

Sybase Update Statistics

update table statisticsupdates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on. update table statistics does not affect column statistics stored in sysstatistics.

Syntaxupdate table statistics table_name [ partition data_partition_name ]

Sybase Update Statistics

update all statisticsUpdates statistics on all columns of the specified table.Again, sampling if specified is used to gather statistics for all non-leading columns of the index.

Syntaxupdate all statistics table_name [ partition data_partition_name ]

Sybase Update Statistics