Re: execution plan and poor statistics

"onedbguru" <onedbguru_at_yahoo.com> wrote in message news:cc278a1b-de27-4353-9c46-57340c25cf90_at_r14g2000vbm.googlegroups.com... This is an easy fix. Stop collecting statistics on the table. - with a caveat.

1. while the table is full - gather statistics - do this one time
2. save these statistics somewhere
3. when your table is full again, restore those statistics.
4. If you never run stats against it again, then it is unlikely that you will collect stats while the table is empty thereby averting your problem.

Doesn't necessarily work - sometimes you have to keep updating the states if you want the plans to stay the same.
This is a side effect of the "linear decay" algorithm that the optimizer uses to pro-rate statistics when the query predicates move outside the low/high column values. If the user has time-based (or sequence-based) data then there may be cases where (in the absence of hinted paths) he will see execution plans change as time passes.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com