Re: URGENT:Q on Order of Update Stats

ssoman@omm.com wrote: > > Hi all, > I was following the informix recommendations (ver 7.2 ) for running UPDATE > STATISTICS on our database, ie. MEDIUM, HIGH on columns heading an index, and > LOW on all other columns of an index. This process is run once a week, and since > it takes over 12hours, I tried to run multiple concurrent sessions of it. My > question is this :- > 1.Should the MEDIUM be run BEFORE the HIGH/LOW ? > 2. Running the MEDIUM after the HIGH, removes all statistical information > for the table from sysdistrib , ie it replaces the HIGH information with the > MEDIUM. Does this affect anything ? Have I lost the whole purpose of doing a > HIGH ?? The MEDIUM should only be run for columns that do NOT form part of an index. Yes, if you run two UPDATE STATISTICS commands on the same column, the last one will take effect. Don't run MEDIUM on the whole table. I think you have lost the whole purpose of doing a MEDIUM, not the HIGH. ;-) > This last weekend, I split the whole processess into 10 different sessions, > put all the lows into one session, put some of the big tables ( HIGH ) into 9 > different sessions, and the medium into another session. I waited for the low to > finish before I ran the MEDIUM, but at the end of it all ( which took only an > incredible 3 hours ), I do not see any information in SYSDISTRIB for a HIGH ( > except for a few tables ). Also, my month-end processess took FOR EVER to finish > this weekend. That's probably right, as the MEDIUM has overwritten it. > SO, have I messed up everything ? If I have, I need to correct it by the > next two days. What is the order to be followed to run UPDATE STATISTICS ?? Not EVERYTHING, just the HIGHs. :-) Just be careful to run only ONE level for each column. If you are running more than one, it will also cost you in time. It doesn't really matter what order, as long as you stick to the following: run LOW for: whole database OR every table in the database run HIGH DISTRIBUTIONS ONLY for: leading columns in each index the first column to uniquely distinguish a composite index from another composite index on the same table, AND all the columns before it. all join columns (normally taken care of by referential constraint indexes) all columns queried with equality (=) filters run MEDIUM DISTRIBUTIONS ONLY for: all the remaining columns and lastly, don't forget to run UPDATE STATISTICS for all stored procedures. If you are going to split these into groups to run in parallel, I suggest putting all table for a specific disk into a group, to balance the IO. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+