Re: UNUSABLE Indexes

"John K. Hinsdale" <hin_at_alma.com> wrote in message news:5cc7fca4-10d2-4208-bd37-778b6fe29513_at_b40g2000prf.googlegroups.com... 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

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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html