Re: UPDATE STATISTICS

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?

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?