Re: UNUSABLE Indexes

From: Jonathan Lewis <>
Date: Thu, 27 Dec 2007 17:01:31 -0000
Message-ID: <>

"John K. Hinsdale" <> wrote in message 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

There are a couple of variations that matter, but you have to start with the knowledge that the update to the index after the insert /*+ append */ is a special optimised variation of index maintenance that keeps the undo and redo to a minimum
(basically by sorting the new entries and using a bulk-insert
into the index leaf blocks).

If the table has been truncated then the index has also been truncated, and the optimised update is similar in resource usage to a 'create' - however, create index does not generate undo, so will be a little more efficient. Moreover, the optimized update has to be logged, but if you end up with a create
(or rebuild) you can choose to do it nologging.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Thu Dec 27 2007 - 11:01:31 CST

Original text of this message