When copying statistics from the source to the target, first collect statistics on the source table, join index, or hash index. If statistics on the source have not been collected before trying to copy statistics from the source to the target, an error results.
- Create a table with n columns.
- Populate the table with rows.
- Use SQL to collect statistics: COLLECT STATISTICS <TableName> Column (Cols);
- To collect statistics about multiple columns, provide a list of columns.
- To collect statistics about a named join or hash index, type COLLECT STATISTICS <TableName> INDEX "IndexName".
- To copy statistics after they have been collected, set the copyStats attribute to true. Below is an XML example of copying the statistics for a table, and the table itself, which is specified by the selection="included" attribute of the table element.
<database selection="unselected"> <name>dbName</name> <table selection="included" copyStats="true"> <name>MyTable</name> </table> </database>
Statistics can be copied without copying the table itself by specifying the selection="unselected" attribute of the table element if the table already exists on the target. This rule also applies to hash and join indexes.
If statistics are copied with the object, the object does not have to exist on the target prior to the copy. If the object already exists on the target, the object is overwritten during the copy, and statistics are copied to the target.
Below is an XML example of copying statistics for a hash index:
<index selection="unselected" copyStats="true"> <name>Cust_Ord_HI </name> <index_database>MyDB</index_database> <index_type>HASH_INDEX</index_type> </index>
The copyStats attribute is valid only for tables, hash indexes, and join indexes.