Since I need to free up some unused space inside a giant tablespace, I basically have to decide if I need to drop the indexes and recreate them after the tablespace being resized, or rebuild the indexes to a different tablespace and rebuild it back after we resized the original tablespace.

One of the deciding factor here is which one is more expensive -- rebuild index or create new index? If they are pretty much the same expense wise, then I would prefer to do drop and recreate, 'cause if we choose rebuild, we'll have to do rebuild twice (first to rebuild to temp tablespace and then rebuild it back to original tablespace).

If rebuild is significant cheaper than create new indexes, then I will prefer rebuild as it will offer uninterrupted availablility of the tables, while drop/recreate index will need some down time (unless they won't mind the poor performance).

What do you gurus think?

Any input is highly appreciated!

Elaine