Rebuilding Index

[himridul]

Hi,

I'm doing direct-load INSERT on nonpartitioned tables that have local indexes.
To avoid the performance impact of index maintenance , I'm dropping the index before the
INSERT and then rebuilding it afterwards .

" rebuilding it afterwards " - Does Oracle create a new index or refresh the previous index ?
I guess that it creats a new index .

Any suggestions will be highly appreciated ...

 

[Dagon]

I think there are some advantages to rebuilding over dropping and re-creating. The index will be rebuilt in the same tablespace with the same storage settings without you having to specify them on the rebuild command.

 

[spicysudhi]

"...I'm dropping the index before the INSERT and then rebuilding it afterwards"

"Dropping" and "Rebuilding" ???

or dropping and re-creating ?

alter index idxname REBUILD; is rebuilding for existing index, but how u r rebuilding after drop ?

-sudhi

 

[aryeh1010]

I won't get on your case about semantics.

When you drop the index it's gone.

You then have to recreate it.

Dagon's point about the easier syntax for rebuilding over creating is true, but for performance dropping and recreating is better, as you are avoiding all the sorts for the new index values as you insert new records into the table.

Aryeh Keefe

 

[Dagon]

Couldn't you achieve the same effect by doing:

alter index xxx unusable;
alter session set skip_unusable_indexes=true;
insert into table ....
alter index xxx rebuild

 

[THoey]

I have noticed that rebuilding is faster, but just a warning. While it is rebuilding, the old index is still there while the "rebuildt" index is being created. So, you need to make sure that your index tables space has the room for at least DOUBLE the current index size.

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[himridul]

Is it true that once it is Rebuilt , it will include the newly inserted data ?
so which one will be faster if I've to impose the index after inserting 100 millions of data into the table.

1. Drop - Recreate

2. alter index ind1 unusable;
alter session set skip_unusable_indexes = true;
insert /*+ parallel */ into table
alter index ind1 rebuild;
alter session set skip_unusable_indexes = false