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).
Rebuilding Index is used so that queries have continuous access to the index while it is being rebuilt. The mechanisms in a Rebuild or a Recreate after Drop is not too different.
When you rebuild, a new Temp Index Segment is created for the index and afer this new temporary segment is populated, the old index is set to temporary and the populated temp segment is redefined as the permanent segment with the same original index name.
I don't understand why you would have to rebuild it twice, unless you are saying your application hard-codes the tablespace name when it creates objects. Even so, once the objects are created, what's the big deal with them being in a different tablespace?
Personally, I prefer REBUILD because you can REBUILD ONLINE and not disturb your users. Dropping an index and re-creating it just makes queries slow for the time you are creating and analyzing your index.
Rebuild on a usable index is performed by reading the old index (unless compute statistics is invoked, i believe). Recreating it is performed by reading the table, of course ... so would you rather read an index twice, or read the table once?
Originally posted by marist89 I don't understand why you would have to rebuild it twice, unless you are saying your application hard-codes the tablespace name when it creates objects. Even so, once the objects are created, what's the big deal with them being in a different tablespace?
Personally, I prefer REBUILD because you can REBUILD ONLINE and not disturb your users. Dropping an index and re-creating it just makes queries slow for the time you are creating and analyzing your index.
Also, both CREATE INDEX and REBUILD (without ONLINE clause) will acquire locks on base tables, and your users WILL complain if the operation is done during the day.
With REBUILD ONLINE, the same lock is acquired just for a moment, and the rest of the operation is done on another separate temporary segment. After rebuild is complete, the new segment replaces the old one. The worst possibility here is some degradation on performance, not downtime.
An ounce of action is worth a ton of theory.
—Friedrich Engels
I agree with Adriano, online rebuild is the best option. Provided u needs to have a enough space for additional index object to be created. Online requires no downtime, but always better to do it on low activity or in maintenance window.
Adriano, all the best to Brazil for copa america !!!!
Watch out for Partition tables if you are rebuilding indexes in peak business times.
Your indexes go for Unusable state while you rebuild and users will be having interruptions in their activities, depending upon which partition data they are working on and especially Primary/Unique Keys of Partition tables.
Reply With Quote
Bookmarks