Sorry, I do not understand what you mean by 'congruous' in this context.
congruous = exhibiting a harmony of parts
That is, when you break down ALL of the parts of "update index statistics
mytable", they are not the same as "update statistics mytable(column) x N"
AFAIK, the stats are column-level and normalised; the Optimiser does not
care about column_a + column_b + column_c stats.
?? I think you are saying that "column group" statistics aren't ever used
by the optimizer. If so, I'd say that's not correct.
Post by Sherlock, Kevin [TeamSybase]update statistics mytable index_one -- ( a, b, c ) --> (a) only
update statistics mytable index_two -- ( d, c ) --> (d) only
update statistics mytable index_three -- ( d, b, c, a ) --> (d) only, again
update statistics mytable(b)
update statistics mytable(c)
The 'update index stats' results in 'update stats table (column) x N'
anyway. The difference in performance of the no and duration of 'updates
stats' executions is obvious, but is anything lost by updating the stats
for the four columns separately (four executions, no repetitions) vs
update index stats mytable' (five executions, one repetition) ? What is
the relevance of the missing congruence ?
Is [C] not the best option (better than [B] and nothing is lost) ?
Um. The reference to the lettering can be a little confusing. Here is what
i think you mean by all the letters:
[A] = "update index statistics mytable"
[B] =
update statistics mytable index_one -- ( a, b, c )
update statistics mytable index_two -- ( d, c )
update statistics mytable index_three -- ( d, b, c, a )
update statistics mytable(b)
update statistics mytable(c)
[C] =
update statistics mytable(a)
update statistics mytable(b)
update statistics mytable(c)
update statistics mytable(d)
Here is how I understand the basic parts of operations; for example:
update statistics mytable(d) ->
scan smallest index or table for column "d" values
create worktable in tempdb
sort worktable
write histogram and column density stats for "d"
update statistics mytable index_two ->
scan leaf level of index_two -
create histogram and column density stats for "d"
create column group density stats for (d,c)
update index level stats for "avg row size", "index level", "cr counts",
etc
So there are two big differences between [B (my breakdown)] and [C (update
stats on each column)] -
1. column group densities, and index level statistics are not updated with
[C]
2. method [C] will ALWAYS require a separate worktable and sorting
operation regardless of the column being updated. As you noted, this is
just a performance difference, but no difference in terms of the end result
per column.
Even if you don't necessarily care about the column group densities and
index level stats being current, then this is still faster than [B] so as to
avoid the performance penalty of (2) above:
[D] (modified [B] disregarding a need for column group densities and
up-to-date index level stats)
---------------
update statistics mytable index_one -- ( a, b, c ) --> (a) only
update statistics mytable index_two -- ( d, c ) --> (d) only
update statistics mytable(b)
update statistics mytable(c)
And noting that even though you don't "want" the column group stats, you
still get them here (for free!) from the first two commands which are a
faster gathering of column stats (avoids tempdb io) for columns "a" and "d".
However, [D] is still not the same as [A] (update index statistics mytable).
There are of course other "equivalencies" to consider too, like:
update index statistics mytable index_one -- ( a, b, c ) --> (a),(b),(c)
histograms serially
update statistics mytable index_two -- ( d, c ) --> (d) only
The interesting part of the above is that the histogram for (c) comes from
index_two (assuming it's smaller than index_one).
Bottom line is that the only "optdiag output" difference between updating
stats on the columns separately as opposed to using the "update statistics
mytable <index>" syntax is the lack of the column group stats, and
up-to-date index level stats (those that are _not_ flushed to systabstats
periodically).
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
--
Feel free to drop me an email, if you which to discuss anything offline