Best way to rebuild index

[7280]

Hi,
I need to rebuild a big index (about 2gb) in my oltp production system.
I have 8 redo logs of 16mb each.
My database is replicated to a standby database.
I want to move it to a stage tablespace and back again
to it's original position.
But I'm afraid about all the archive logs are going to be created.
What is the best way to rebuild the index?
Please advice me, thanks

 

[SantaMufasa]

7280,

The fastest method to rebuild and index and to avoid all the on-line/archive redo logging is:

alter index <owner>.<index_name>
    rebuild parallel tablespace <new_tsname>;

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[eagertotry]

alter index..rebuild will avoid logging only if either the index has nologging attribute already set or alter index..rebuild includes nologging clause. Also, using nologging would mean the index will be marked corrupted in standby database and your would've to rebuild it there before you can use it.

 

[SantaMufasa]

Good catch, Eager. I didn't even notice that I had omitted the "nologging" specification from my code example. (Until now, I had thought it was there.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.