Re: UPDATE STATISTICS

When you do a UPDATE STATS is broken into 2 phases: 1) update the information based on indices 2) update the distribution information. For update stats low only the first phase is run... In this phase the following is done in sequence: 1) The partition page is read to update the nrows and npused columns of the table (for a fragmented table, each fragment's nrows and npused is updated using the partition page) 2) Update column statistics of the table if there are columns with indexes and the user has not specifically requested "with distributions only". Inside 2) the engine loops through each index and checks if the keys in the index are part of the column list specified by the user if yes, it'll scan the index to gather the index statistics if not, it'll skip this scan. That's why when you specify the update stats of a column with a index it takes longer than when a non-indexed column is specified, since step 2) is skipped for this. So, if you don't specify any columns in the update stats it'll essentially scan through all the indexes in order to get the statistics, and if the table is pretty volatile i.e. lots of updates/delete operations then while scanning it'll put the 'marked for deleted' keys to the B-Tree cleaner thread which can slow up the things a bit too. The update stats has been improved a lot with parallelization and also better usage of the memory / heap allocated for the process ... if you want the white paper on the improved update stats, let me know I'll send it over to you in a seperate email. HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi olivier.christol@rf o.atmel.com To: informix-list@iiug.org (Olivier CHRISTOL) cc: Sent by: Subject: Re: UPDATE STATISTICS owner-informix-list @iiug.org 08/22/2003 05:41 AM Please respond to olivier.christol "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 sending to informix-list