Re: UPDATE STATISTICS
I have found the same thing on numerous occasions with Peoplesoft. With the
odd exception, distributions have just served to soak up CPU time.
Mark
----- Original Message -----
From: "Thomas J. Girsch" <tgirsch@worldnet.att.net>
To: <informix-list@iiug.org>
Sent: Friday, August 22, 2003 19:10
Subject: Re: UPDATE STATISTICS
> Thanks, Art. Already have dostats, as well as a home grown updstats
> program. We've found that in our case, our application runs much better
> WITHOUT distributions. LOW only stats are best for us.
>
> But my ultimate question has not been answered to my satisfaction.
Imagine
> the following table:
>
> CREATE TABLE tab1 (
> a INTEGER,
> b CHAR(5),
> c DATE,
> d CHAR(30)
> );>
> CREATE UNIQUE INDEX ix_tab1_00 ON tab1(a,b);
> CREATE INDEX ix_tab1_01 ON tab1(b);
> CREATE INDEX ix_tab1_01 ON tab1(c);>
> If I run:
>
> UPDATE STATISTICS LOW FOR TABLE tab1(a,b);
> UPDATE STATISTICS LOW FOR TABLE tab1(b);
> UPDATE STATISTICS LOW FOR TABLE tab1(c);>
> ... it updates the sysindexes records, as well as the nrows in systables.
>
> If I run:
>
> UPDATE STATISTICS LOW FOR TABLE tab1;>
> ... it also does these things. But it takes worlds longer, even in IDS
9.3.
> So my assumption, possibly incorrect, is that the latter command must be
> doing something _else_, something that the previous three UPDATE
STATISTICS
> commands didn't do. Updating some other part of the system catalog,
> perhaps? If yes, then what exactly is it doing in addition to the above,
> and what benefit can I expect to see from that? (Or what penalty for NOT
> doing that?) If no, then why does the latter command take SO much longer?
> Wouldn't that then be *gasp* a bug?
>
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> news:pan.2003.08.22.14.14.03.774652.10594@bloomberg.net...
> > On Thu, 21 Aug 2003 21:12:14 -0400, Thomas J. Girsch wrote:
> >
> > If you operate on the one column that is indexed then it only collects
> > info on the one column and updates just that one matching index's
> > sysindex(sysindices) record. If you update stats on the entire table it
> > has to collect data for all of the columns. That requires multiple
> > sorts.
> >
> > Get my dostats utility. It performs the minimal update stats statements
> > as recommended in the Performance Guide for you using optimized
> > statements.
> >
> > Dostats is part of the package utils2_ak available from the IIUG
Software
> > Repository.
> >
> > Art S. Kagel
> >
> > > Imagine a table tab1 with a bazillion rows and five columns. There is
> > > one index on the table, on column A.
> > >
> > > If I do:
> > >
> > > UPDATE STATISTICS LOW FOR TABLE tab1(col_a);> > >
> > > ... it runs very quickly. But if I just do:
> > >
> > > UPDATE STATISTICS LOW FOR TABLE tab1;> > >
> > > ... it takes hours, maybe days. So what exactly does the latter
command
> > > do, that the former command does NOT do?
>
>
sending to informix-list