After a short conversation with Alex Gorbachev, I realized that maybe I should have added some implementation details about the “two-phase removal of data” approach for reducing table volatility.
First, the approach does not reduce the volatility of the content of the tab view, it reduces the volatility of what the CBO sees or assumes about the tab view. But, again, that is all that matters…
Second, please do not create a histogram on the deleted column of tab_internal table, even though the column would likely be heavily skewed. Doing so would introduce volatility because the histogram would represent the deleted/active distribution as of when the stats are gathered. What we need is the “standard” 50/50 split we get when the column does not have a histogram. Make sure that there are exactly two distinct values for the deleted column in tab_internal table.
Third, an index (regular of function-based /covering only the “N” value/ ) on the deleted column in tab_internal would be quite useful.
On a different note, someone from the back of the hall, I think that was Kerry Osborne, but I am not quite sure, mentioned that the JUST_STATS package should explicitly invalidate all cursors that use the table we are gathering stats on. I answered that I thought the package did invalidate cursors, but I was incorrect. Anyway, the current version does invalidate cursors (NO_INVALIDATE=> FALSE). Thanks a lot for this observation.