Re: UPDATE STATISTICS

"Thomas J. Girsch" <tgirsch@worldnet.att.net> wrote in message news:<O5e1b.97166$X43.68994@clmboh1-nws5.columbus.rr.com>... > 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? Dear All, I experience the same problem. The update statistics low takes about 1 sec when I do the update on all columns separetly and five hours when I do the update statistics low on the table . The update statistics low for a column does not update indexes whereas the low for the table do. Is anyone here has an idea on how you can improve time of an update statistics low for my_table in IDS 7.31UC5 and how you can limit the impact on users. Thanks in advance for any helpfull remarks Olivier

Olivier CHRISTOL wrote: > The update statistics low for a column does not update indexes whereas > the low for the table do. > It is my understanding (but I have never tested) that if you have a single-column index and you update stats low on that column, the stats for that index are updated. It is my further understanding that for multi-column indexes if you update stats low and list all the columns in that index, the stats will be updated. So, table T has columns C1, C2, C3, C4 and two indexes: one on C1 and one on C2, C3: update statistics low for table T(C1); update statistics low for table T(C2, C3); would be the proper method to update stats low. I have further found (through experience and testing) that following the recommendations in the Guide to Performance for your particular flavor of Informix tends to both perform well when running and also cause good performance for other queries. Yes, there are exceptions. Always test first.

"Olivier CHRISTOL" <olivier.christol@rfo.atmel.com> wrote in message > Dear All, > > I experience the same problem. The update statistics low takes about 1 > sec when I do the update on all columns separetly and five hours when > I do the update statistics low on the table . > The update statistics low for a column does not update indexes whereas > the low for the table do. > > Is anyone here has an idea on how you can improve time of an update > statistics low for my_table in IDS 7.31UC5 and how you can limit the > impact on users. > > Thanks in advance for any helpfull remarks > > Olivier What I've found is that if you do an UPDATE STATISTICS LOW for each _index_, that's all you need to do. Unless someone can explain otherwise. Example, you've got a table with columns a, b, c, d, and e, and you've got a composite index on a,b,c; a composite index on b, c, a; and a singleton index on column b; you could do this: UPDATE STATISTICS LOW FOR TABLE tab1(a,b,c); UPDATE STATISTICS LOW FOR TABLE tab1(b,c,a); UPDATE STATISTICS LOW FOR TABLE tab1(b); This would update all the sysindexes rows appropriately. Any one of the above would update nrows in systables appropriately. And running all three of these combined would take considerably less time than just running UPDATE STATISTICS LOW FOR TABLE tab1; So my ultimate question becomes, why would you ever run UPDATE STATISTICS LOW FOR TABLE tab1?