RE: The truth behind UPDATE STATISTICS...

> 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) The only thing that update statistics does apart from modifying the system catalogs is to re-parse any stored procedures. This is clearly nothing to do with your problem. It sounds as though your query has confused the optimiser and caused it to make the wrong decision. I've seen this happen if you have a single table joined to four or more other tables which in turn join to other tables (if you can picture that). Also if you have filters scattered widely across many tables. It could be time to split the query into two. If you mail me the sqexplain.out I'll see if I can suggest anything. akent@cix.compulink.co.uk (Andy Kent) ------------------------------------------------ Freelance Informix Database Specialist, Redland, Bristol, England