Re: long running select min(timestamp) query
Date: Thu, 31 Jan 2008 18:36:21 +0100
On 31.01.2008 14:49, bobdurie_at_gmail.com wrote:
> I probably should've prefaced this entire thread with - I'm an
> applications developer, not a dba :) I know this is a lame excuse,
> but i simply don't have the time nor mandate to become an oracle guru,
> i'm simply tasked with getting an application to work with oracle (and
> a variety of other db's). Every once in a while i get cycles to deal
> with issues like this, but i certainly don't spend enough time doing
No worries, it will just take a bit longer to become an Oracle guru. Eventually you'll get there. :-)
> Anyways, Charles has helped me out bigtime by pointing me in the right
>>> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf >>> Pages 133-135 show what is happening in your situation. It appears that this is one of the few cases where an index may need to be rebuilt...
> I rebuilt the index, performed the search, got a result instantly,
> only 3 consistent gets!!!
> One followup question, that you'll all likely laugh at. I got into
> this "mess" by having a table that we frequently perform delete from
> TABLE where DATE < ? - this situation is going to continue to arise.
> Should i simply schedule frequent index rebuilds? I understand
> partitioning the data is probably the way to go, but what is frequent
> rebuilds the simplest solution here (by simple, i mean least knowledge/
> testing/sql involved)?
If your deletion is part of a batch job that is run infrequently then it's probably easiest to just add the index rebuild to the batch. Note the ONLINE flag which will slow down the rebuild a bit but do not require locking of the whole table. So your app can continue almost normally.
If you use partitioning in pre Oracle 11 you need to set up a job that will create partitions properly. And in all versions you need another job for the deletions (again assuming they are done on a regular basis).
For this not too uncommon scenario of data coming and going time based this MIN issue is really annoying. IMHO Oracle should do something about it - at least it would be nice if there was an option during index creation that would enable automatic index tree pruning at the cost of a bit of performance.
robert Received on Thu Jan 31 2008 - 11:36:21 CST