SEO

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