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 ?? > 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. > 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 ?? > > As always, I appreciate your response(s) > > Thanks > > Sujata Sujata: It sounds like something has stuffed up. What is important is to make = sure one process does everything relating to any one table, ie don't put = LOWs in one, MEDIUMs (media?) in another etc. Write it in such a way = that each process will be always operating on a different table, and you = can be sure that the results will be the same as if you just ran one long = SQL. Just faster ;-) The book says MEDIUM DISTRIBUTIONS ONLY, then HIGH (by column), then LOW. = I would follow that. I use a script to create and load a table of table-names, as well as a = "size indicator" - rowsize * nrows (this all comes from systables). The = script then launches 'n' processes of the same 4GL executable in the = background, each of which pick off tables from this list in descending = size order, and complete all the statistics. Doing it like this ensures = that irrespective of how many concurrent processes you have they should = share the work reasonably equally. You should be able to duplicate this effect by generating 5 scripts. I = use 4GL because it generates all the UPDATE STATISTICS statements for me = from systables as it executes. [ Pop Quiz: write the SQL to identify the = columns which are part of an index, but not the leading column of one. = Don't forget about indexes sorted in descending order... ;-) ] I usually run 5 concurrent processes. I have tried other numbers, but = any more doesn't seem to improve results. (6 cpu machine) cheers RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+