Difference between different update statistics commands in ASE

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 08, 2017 at 06:30 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • May 15, 2017 at 06:46 PM

    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).

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content