highly volatile tables
558032Jul 23 2010 — edited Jul 26 2010What are the options on 11.1 for dealing with a table whose contents are highly volatile? I have a table where the contents change so frequently that for the same where-condition a query may return 0, a few, or a few thousand rows at any given time. I have removed the statistics on this table so that dynamic sampling goes into effect when a query is hard parsed, but even that isn't enough. If the result of a hard parse is cached and then the data changes, a soft parse that uses what's cached could end up with very poor performance.
I know I can force a hard parse every time (http://oracle-randolf.blogspot.com/2009/02/how-to-force-hard-parse.html) but I don't like what that does to the cache. But is this really my only option?