Re: Update statistics question

david.ashby@workcover.nsw.gov.au wrote: [SNIP] > From these results I would contend that the update statistics > recomendations in the release notes pertaining to the final step > "update statistics low" are unnecessary. It would be better to run > update statistics low for the whole table not for specific columns. Actually, the recommendation is to do an UPDATE STATISTICS LOW on the entire index key to calculate the key counts and 2nd high and 2nd low key values for each index. Apparently, while faster, an UPDATE STATISTICS LOW on the entire table is not sufficient. (The exception to this rule is for single key indexes, the LOW stats will have been calculated when the HIGH on that column was run.) You realy do have to go through all five of the steps in the 7.2 release notes recommendations: 1) Update statistics MEDIUM on all columns in a table that do not head any index with the DISTRIBUTIONS ONLY clause. (You can do the entire table since the extra time is minimal.) 2) Update statistics HIGH on all columns that head an index using a separate statement for each column. 3) Update statistics HIGH for the first column that differs when two or more indexes begin with the same subset of columns. 4) Update statistics LOW for all of the columns of each multi-column index. 5) If you join tables on columns that do not head indexes you MAY gain from UPDATE STATISTICS HIGH ... DISTRIBUTIONS ONLY on those columns as well. Remember the goal of the recommendations was not to save time when updating statistics, but to provide the best information to the optimizer without spending more time than neccessary. Art S. Kagel