/scripts/ahub.form.attachments.js
0

Difference between different update statistics commands in ASE

May 08, 2017 at 05:49 PM

225

avatar image

I'm trying to figure out the difference between these update statistics commands (with descriptions copy/pasted from the documentation. Bold added by me):

  • update statistics <table> - "Updates information about the distribution of key values in specified indexes, for all columns in an index, table, or partition"|
  • update index statistics <table> - "Updates the statistics for all columns in an index."
  • update all statistics <table> - "Updates all statistics information for a given table
    ..snip..., regardless of whether they are indexed"

Does 'update statistics <table>' update stats for all columns in a table? I think it *doesn't* because "update all statistics" says it does all columns. Maybe 'update statistics <table>' only updates leading index columns? Does it update non-indexed columns?

Does 'update index statistics <table>' update stats for all columns in all indexes in a table? (I think it *does*)

Thanks in advance
Ben

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mark A Parsons May 08, 2017 at 06:30 PM
2

The update statistics command has options that include columns, so I'm guessing the (documentation) author was trying to save space/typing by jamming everything into a single sentence. I'd suggest taking a look at the comments associated with the various examples to get a better idea as to what happens when various options are provided to the update statistics command. [Taking with a grain of salt what you see in the examples, ie, there are cases - in the various manuals - where examples and/or the associated comments are confusing or just plain wrong.]

Generally speaking, histograms are generated as follows:

  • update statistics => leading column of index(es)
  • update index statistics => all columns in index(es)
  • update all statistics => all columns in table

Easy enough to test/verify:

  1. delete statistics
  2. run desired update*statistics command
  3. run optdiag to see which columns have histograms
Share
10 |10000 characters needed characters left characters exceeded
Ben Slade May 15, 2017 at 06:46 PM
0

So I ran a quick test and verified the following:

  • update statistics <table> - updates leading columns of indexes, and also updates statistics for "column groups" within the index. Ie, if your index has fields col1, col1, & col3 then "update statistics" will update statistics for col1, for col1 & col2, and for col1, col2, & col3
  • update statistics <table> <index> - is the same as "update statistics", but just for one index.
  • update index statistics <table> - does everything from "update statistics" plus update stats for individual columns in the index.
  • update all statistics <table> - updates stats for all columns (and index column groups as described above)

So, yes, your assumptions are true.

I guess my point is the documentation saying:

  • update statistics <table> - "Updates information about the distribution of key values in specified indexes, for all columns in an index, table, or...

Is just flat out misleading. I'll create an incident (FWIW).

Share
10 |10000 characters needed characters left characters exceeded
Skip to Content