Sybase Update Statistics

*

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.

Sybase Update Statistics 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 percent Updates statistics on the leading columns of all indexes on the specified table or partition or the leading column of a specified index.

Syntax update 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 ? 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.

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 Updating partition statistics This command generates distribution information for a particular partition in a table. Syntax update all statistics table_name [ partition data_partition_name ]

Sybase Update Statistics update table statistics updates 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. Syntax update table statistics table_name [ partition data_partition_name ]

Sybase Update Statistics update all statistics Updates 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. Syntax update all statistics table_name [ partition data_partition_name ]