Re: onstat -u, B-flag

Alexander Selg wrote: > > June Tong wrote: > > > > Alexander Selg (alexander.selg@siemens.at) wrote: > > The Problem was, that our UPDATE STATISTICS HIGH, which runs every day > for 29 tables, didn't come to all tables because it needs to much time > (after 24h the process control killed the update statistics process and > restarted him) > > I found out that an UPDATE STATISTICS HIGH on the index-attributes and > an UPDATE STATISTICS MEDIUM on the other attributes is enough for the > optimizer to make an index scan (instead of sequential) - and it needs > only half the time of an UPDATE STATISTICS HIGH on the whole table. > Maybe it's also enogh to use LOW or DITRIBUTIONS ONLY - I don't know. > > On the other side, maybe it's not necessary to run UPDATE STATISTICS > every day. The tables have about 3.000.000 rows; 200.000 of them are > deleted every day by one process (with "delete from atable where > adatetime <= "xx.xx.xxxx xx:xx:xx"" on adatetime there is an > index(dulps) and on adatetime and another attribute there is an unique > index) and 200.000 rows are inserted every day (with "insert into atable > values(.....)"). > How many % of a table must change, so that it's necessary to run UPDATE > STATISTICS ? If only the row count has changed but the general distribution of keys has stayed the same you only have to do UPDATE STATISTICS LOW to update the row counts and 2nd high and low key values. Also check the release notes for the BEST schema for updating statistics when the distribution of keys does change. (BTW if the distribution of key changes only affects one index or column only those columns need to be updated.) I have a utility, dostats.ec, which can perform this complex on one or several databases/tables. Send me email if you have ESQL/C and would like a copy. Art S. Kagel