Re: Update Statistics
That's what I'm trying to get to the bottom of.
The current guidelines are
update statistics medium for <table> distributions only;
update statistics high for <table> (<columns>) distributions only; # where columns is every column that heads an index and the first
differing columns of composite indexes that begin with the same
subset of columns
update statistics low for <table (<columns>); # where columns is every column that is part of an index
Check out the following links for more detailed information
http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/informix/0211desai/0211desai.pdf
(understand the optimizer)
http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html
(tuning update statistics)
http://publibfp.boulder.ibm.com/epubs/pdf/8344.pdf (9.3 performance tuning
guide, look for update statistics in the perf guide for your version)
http://groups-beta.google.com/group/comp.databases.informix/browse_frm/thread/a852f14f35a0038d/6a2d537ae8d21b02?q=update+statistics+repeated+column&rnum=1&hl=en#6a2d537ae8d21b02
(cdi post, but based on 7.21 recommendations)
Andrew
"Avichal A Narayan" <avichal@cjpatel.com.fj> wrote in message
news:1118381864.2781eba6a46aaf19356644aa3a30653b@teranews...
>
> While still on update statics.... so what is most advisable ???
> ..To do update statistics medium first and then do update staticstics high
> ?????
> We encountered a problem when we did update statistics medium. After
> sometime the system gets very slow.
>
> Any advise ??
>
> Avichal
>
>
> ----- Original Message -----
> From: "Art S. Kagel" <kagel@bloomberg.net>
> To: <informix-list@iiug.org>
> Sent: Friday, June 10, 2005 8:25 AM
> Subject: Re: Update Statistics
>
>
> > 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