Re: UNUSABLE Indexes

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

   http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1009887

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