Re: Update Statistics Guidelines

Superboer schrieb: >> 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. > > Superboer. > This was true at least upto 9.40 versions. Findings are, though, in V10FC5 or later, that if a table has or had less than 101 rows AND the optimizer knows this, a sequential scan will will be performed. Out workaounds are - either optimizer hint - or faked update statistics LOW (populate a table with the same name and a resonable no of rows, run update statistics LOW for table, drop it, do you regular work and never run update statistics for this table again) dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe