Re: The truth behind UPDATE STATISTICS...

Rus Ambler (rizzo@fourgee.demon.co.uk) wrote: : In article <3nocjb$1nc@cssun.mathcs.emory.edu> : huddles@emspo01.hasting.com "Huddleston, Joe" writes: : > : > it sets the number of rows the system catalog thinks the table has, which : > will affect which indices the optimizer will use. : > : > -joe- : > (huddles@hasting.com) : All these values have been checked - what I need to know is what else, : *APART* from the various system catalogs does UPDATE STATISTICS alter? : I re-iterate my problem: : Session 1 : [isql system], QUERY, NEW, [update statistics for table iplas], : RUN, CHOOSE, "iplas_4", RUN : Session 2 : [isql system], QUERY, CHOOSE, "iplas_4", RUN : Both these sessions running on the same box, at the same time, using two : terminals. I hit R for run on both keyboards to run the iplas_4.sql file. : Session 1 returns with an instant answer, an examination of the sqexplain.out : file reveals that the optimizer has taken the correct index path. : Session 2 takes five minutes, sqlexplain.out reveals that the index path used : is incorrect. : The select statement is *exactly* the same in both cases - the only difference : being the fact that one isql session has had an UPDATE STATISTICS performed : before the select is run. Both selects work on the same instance, same : database, same table, same level, same uid, same everything. : Ergo, UPDATE STATISTICS must either be : : a) Modifying some parameters in shared memory for the current sqlturbo session, : - or - : b) Updating the right/wrong system catalogs (perhaps the ones in the : bundlespace for example) There is a third possibility which seems plausible to me (but I hope I am wrong, I have not had the opportunity to carry out any testing to support or reject this hypothesis). Maybe: c) The second session does not "see" a sysindexes entry because it is locked (or otherwise made unavailable) by the UPDATE STATISTICS in the first session. : According the R&D guys at Informix in the US, what i'm getting "is impossible". In which case they will hopefully blow me out of the water. : Trouble is, I can do it to order - every bloody time! : -- : --------------------------------------- : Rizzo rizzo@fourgee.demon.co.uk : --------------------------------------- -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.