Re: UNUSABLE Indexes
Date: Thu, 27 Dec 2007 08:30:15 -0800 (PST)
On Dec 27, 8:51 am, bhonaker <bhona..._at_gmail.com> wrote:
> On Dec 26, 5:10 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> > Oracle 10.2.0.3.0, Windows 2003 Server
> > Is there ANY possible way that an index marked as unusable can become valid
> > other than by either rebuilding or recreating?
> > Thanks
> TRUNCATEing a table will mark the indexes as VALID. I found this in
> Jeff Moss's blog, more info here:http://oramossoracle.blogspot.com/2006/07/truncate-command-marks-prev...
What's interesting is that in the thread on Moss's blog, the original poster was executing, on a table with index(es):
- TRUNCATE table
- INSERT /*+ APPEND */ (presumably many rows)
so actually you might think for speed he would actually _not want_ the index to be marked usable following the TRUNCATE, and instead leave the index maintenance to be done "batch" at the end.
But a glance at Oracle docs
seems to imply that is indeed what happens (from above):
"Index Maintenance with Direct-Path INSERT
Oracle Database performs index maintenance at the end of direct-path INSERT operations on tables (partitioned or non-partitioned) that have indexes. ... You can avoid the performance impact of index maintenance by dropping the index before the INSERT operation and then rebuilding it afterward."
However, I'm having trouble grocking the above; it seems to say that for insert-with-append that Oracle does the index maintenance all at the end (what you want). But the last sentence seems to suggest the drop/rebuild would be better, but to me seems like that would be roughly the same cost.
John Hinsdale Received on Thu Dec 27 2007 - 10:30:15 CST