RE: Update Statistics
Update statistics low doesn't build distributions, the way high does. Itcompiles information about the structure of the indices (depth, leaf
nodes, clustered-ness, second-highest and second-lowest values, etc),
and places them in systables, sysindexes (or sysindices, in IDS 9.4...)
and syscolumns, whereas medium and high build the distributions of the
actual data and put it in sysdistrib. Therefore, it is important to run
low and either medium or high for each column. We generally run low for
the entire table, high for the leading edge of indices, and medium or
high (depending on time and data size) for non-leading edge columns.
Hope that helps.
DC
-----Original Message-----
From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
On Behalf Of Andrew Ford
Sent: Monday, June 13, 2005 1:53 PM
To: informix-list@iiug.org
Subject: 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>) distributionsonly;
> # 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
> >
> >
>
>
sending to informix-list