Re: Update Statistics Guidelines

Superboer wrote: >> Sometimes not even this has been enough as we have one frequently-used >> transient table that can contain anything from 0 to 50000 rows. If >> update statistics was last ran when there were 0 rows the engine favours>> sequential scans which are very inefficient when the table gets large. >> We ended up with this coding optimiser hints into our code to force use >> of the indices. This somewhat obviates the need for "update statistics". > > where is OPTCOMPIND set to?????? if itś 2 then you can end up having > the above problem. > if it is 0 you --should-- not get this problem. It's definitely set to 0 on all our systems. This aspect of Informix is a little bugbear for us. Ben.

Ben Thompson wrote: > Superboer wrote: > >>> Sometimes not even this has been enough as we have one frequently-used >>> transient table that can contain anything from 0 to 50000 rows. If >>> update statistics was last ran when there were 0 rows the engine favours>>> sequential scans which are very inefficient when the table gets large. >>> We ended up with this coding optimiser hints into our code to force use >>> of the indices. This somewhat obviates the need for "update statistics". >> >> >> where is OPTCOMPIND set to?????? if itś 2 then you can end up having >> the above problem. >> if it is 0 you --should-- not get this problem. > > > It's definitely set to 0 on all our systems. This aspect of Informix is > a little bugbear for us. > > Ben. Why don't you just run update statistics when the table is populated with 50,000 and then not run it again?

TBP (The Big Potato) wrote: > Why don't you just run update statistics when the table is populated > with 50,000 and then not run it again? That does work but we have several people who could decide to administer that server and run "dostats" so managing that kind of thing is difficult. We require a low-administration solution and the optimiser hint works well. Regards, Ben.