Fastest Way to Rebuild Oracle Index?

Question: I have to rebuild several very large indexes and I need to know the fastest way to rebuild the index. 

What are the performance options for a fast index rebuild?

Answer:  When Oracle rebuilds an index, you have several tuning options to make it faster:  

1. PARALLEL Scan:  Start an index rebuild with a full- scan, and this full-scan can be parallelized according to your cpu_count.
   
   
2. NOLOGGING:  You can also use the NOLOGGING option for super-fast index rebuilding.  The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery.  Using nologging with create index can make index rebuilding up to 30% faster.
    
3. Partition the index:  If you have purchased the partitioning option, you can rebuild a local partitioned index faster than a single large index.
   
   
4. SSD:  Some shops will use temporary solid-state disk to speed-up the initial index writes and move the index to platter disk storage at a later time.
   
   
5. Parallel index rebuild jobs:  Also, remember that if you have the spare CPU and the indexes are on different disks, you can submit many index rebuild jobs simultaneously.  On a large server you can simultaneously rebuild dozens of indexes, each using parallel query, sort of a parallel parallelism for fast index rebuilds.  

You can also automate and schedule the rebuilding of indexes.  

Also see these important notes on index rebuilding and rebuild performance.

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright ? 1996 -  2012  All rights reserved. Oracle ? is the registered trademark of Oracle Corporation.