RE: The truth behind UPDATE STATISTICS...

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) According the R&D guys at Informix in the US, what i'm getting "is impossible". Trouble is, I can do it to order - every bloody time! -- --------------------------------------- Rizzo rizzo@fourgee.demon.co.uk ---------------------------------------