Re: UPDATE STATISTICS
On Fri, 22 Aug 2003 19:10:59 -0400, Thomas J. Girsch wrote:
See below:
> 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?
The only thing I can imagine the last command doing that the former three
would not do is to calculate the colmin and colmax for the last,
unindexed, column 'd'. As that columns is 2.33 times as large as the
other three columns combined the sorting time alone caused by adding that
key will be more than 3x the total sort time for the other three
commands. Try doing the UPDATE STATISTICS LOW FOR TABLE tab1(d); and see
what happens. If the runtime of that one command explains most of the
difference between the whole table command versus the three index only
commands then I'd count your understanding as complete.
<SNIP>
Art S. Kagel