update statistics

Updates information about the distribution of key values in specified indexes. Also updates row count information.

update statistics table_name [index_name]

When the update statistics command is issued against a proxy table, Component Integration Services provides meaningful statistics on the remote table and the given index or on all indexes if no index is specified. The results are used to construct a distribution page for each index. This distribution page is stored in the database. When a new distribution page is created for an index, any previous distribution page for that index is freed.
Using update statistics, Component Integration Services creates extremely accurate distribution statistics for remote tables. This information is used to determine the optimal join order, giving Component Integration Services the ability to generate optimal queries against remote databases which may not support cost-based query optimization.
When Component Integration Services forwards the command to a remote server, the table name used is the remote table name, and the column names used are the remote column names. These names may not be the same as the local proxy table names.
Obtaining information on an index, and especially on a number of indexes, can be time consuming on large tables. Trace flag 11209 can be used to indicate that update statistics is to obtain row count only. When this flag is on, previous distribution pages for indexes are not replaced.
Component Integration Services retrieves row count information even if no indexes exist.

If the table on which the statistics are requested has no indexes, Component Integration Services issues the following command:
```
select count(*) from table_name 
```
It is also the only command issued when trace flag 11209 is on.

If the table has an index and the index is specified in the command, Component Integration Services issues the following commands:

select count(*) from table_name

select count(*) column_name [,column_name, ...]
 from table_name 
 group by column_name [,column_name, ..]

The column name(s) represent the column or columns that make up the index.

For example, when the following command is issued:

update statistics customers ind_name

Component Integration Services issues:

select count(*) from customers

select count(*) last_name, first_name 
 from customers
 group by last_name, first_name

If the table has one or more indexes but no index is specified in the statement, Component Integration Services issues the select count (*) once, and the select/order by commands for each index.

The processing of update statistics in this server class is identical to that of server class ASEnterprise described above.

The processing of update statistics in this server class is identical to that of server class ASEnterprise described above.

The processing of update statistics in this server class is identical to that of server class ASEnterprise described above.

The processing of update statistics in this server class is identical to that of server class ASEnterprise described above.
If the direct_connect indicates that is cannot handle the group by or the count(*) syntax, statistics are not collected for the direct_connect.

The processing of update statistics in server class db2 is identical to that of server class ASEnterprise described above.