Re: The truth behind UPDATE STATISTICS...

[Graeme Sargent (graeme@pyramid.com) wrote] > Rus Ambler (rizzo@fourgee.demon.co.uk) wrote: > : In article <3nocjb$1nc@cssun.mathcs.emory.edu> [good stuff snipped] > : 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". And we all have the utmost respect for Informix's technical support. ;-) Now, I *know* Rizzo is not using an ANSI standard database, but if he were, the fact that an implicit transaction is not being closed might have something to do with it. Could ISQL be doing the same sort of thing: Opening an implicit transaction that needs to be committed before it (the UPDATE STATISTICS) is actually flushed? Just a thought. Rizzo, is this HP or AIX or something else? At DSP or on site somewhere? +------------------------------------------------------------------------+ | Bob Payne Internet: bobp@dev.dsc.dalsys.com | | Systems Engineer : bpayne@netcom.com | | Dallas Systems Corporation Voice: (214) 383-2238 | +------------------------------------------------------------------------+