Re: UNUSABLE Indexes

Jonathan Lewis wrote:
> "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.

Might want to look at:
DBMS_INDEX_UTL
http://www.psoug.org/reference/dbms_index_utl.html

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org