Re: UPDATE STATISTICS

A bug... May it be... Chucho! Thomas J. Girsch wrote: > 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? > > > > > -- Atte, Jes's Antonio Santos Giraldo jeansagi@myrealbox.com jeansagi@netscape.net sending to informix-list