Re: Update Statistics
But...
If every column in an index appears in the update statistics high, therefore
eliminating the need for an update statistics low statement all together, is
that a bad thing?
Will an update statistics high without the 'distributions only' clause scan
the indexes and send btree cleaner requests to clean up deleted index pages
or is update statistics low the only operation that does that?
Andrew
"Andrew Ford" <andrewford@austin.rr.com> wrote in message
news:73hre.43979$PR6.32863@tornado.texas.rr.com...
> Or...
>
> update statistics low for table <table> (<columns>);> # for every column in an index that doesn't appear in the high run
>
> update statistics medium for table <table> (<columns>) distributions only;> # for every column of a table not in the update stats high
>
> update statistics high for <tablename> (<column list>);> # for every column that heads an index and the first differing columns
> of
> composite indexes that begin with the same subset of columns
>
> moved some colums from the low run to the high run and removed the
> distributions only clause of the high run.
>
> Andrew
>
> "Andrew Ford" <andrewford@austin.rr.com> wrote in message
> news:_vgre.43956$PR6.7994@tornado.texas.rr.com...
> > I'm not sure I agree 100% with running update stats medium on the entire
> > table.
> >
> > Sure, there is no performance penalty with regards to the update stats
run
> > times but the optimizer will be using less accurate medium statistics
for
> > heads of indexes to generate query plans until update stats high for
those
> > columns completes successfully. This may not be a problem for engines
> that
> > do light processing during maintenance windows, but for 24/7 engines
this
> > *could* be a problem.
> >
> > Other than the extra human work needed to generate a column list for
> update
> > stats medium, are there any downsides to running update statistics in
the
> > following way for newer versions of IDS that have the new update stats
> > performance improvements (9.3xC2 and up)?
> >
> > update statistics low for <tablename (<column list>); # for everycolumn
> in
> > an index
> > update statistics medium for <tablename> (<column list>) distributions> only;
> > # for every column not in the update stats high
> > update statistics high for <tablename> (<column list>) distributionsonly;
> > # for every column that heads an index and the first differing columns
of
> > composite indexes that begin with the same subset of columns
> >
> > Also, the order in which update statistics is run should no longer
matter
> > since we are not overwriting anything.
> >
> > Andrew
> >
> > "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> > news:42A8A5CD.3000204@bloomberg.net...
> > > Andrew Ford wrote:
> > > <SNIP>
> > >
> > > > Am I wrong in thinking that to update statistics against a table you
> > only
> > > > need 3 update statistics statements.
> > > >
> > > > low - every column that appears in an index
> > > > high - every column that heads an index and every column in a
> composite
> > > > index that is the first column that differs when multiple indexes
have
> > the
> > > > same columns at the head of the index (i.e. (a, b, c, d) and (a, b,
> c,
> > e)
> > > > run high for d and e)
> > > > medium - every column not included in the high stats
> > > <SNIP>
> > >
> > > Specific comments:
> > >
> > > medium - The medium should be done first and it can be done on all
> > columns.
> > > Because of the sampling algorithms used it's just as fast to do all
> > > columns as to do the subset that's not heading indexes, so dostats
just
> > does
> > > a MEDIUM on all columns first then overwrites the medium stats with
high
> > > stats where needed.
> > >
> > > low - Again, yes for stats quality, no for efficiency if you are
running
> > an
> > > older release of IDS.
> > >
> > > high - Same comments as for low. On newer IDSs do it all together, on
> > older
> > > ones, break it up.
> > >
> > > Art S. Kagel
> >
> >
>
>