Friday, October 18, 2013

How to speed up index rebuild


ALTER SESSION FORCE PARALLEL DDL;
ALTER INDEX <schema>.<index_name> REBUILD PARALLEL 4 NOLOGGING;

Note the "NOLOGGING" - this tells oracle that it will not log the *creation* of the index.
This result in faster creation time. The index would not be recoverable during a recover operations, since oracle would not have the information in its redo logs. This may be perfectly acceptable for indexes, since they can easily be recreated anyway if we ever have to recover the database.

After the index is created, remove the PARALLEL clause to impose normal, non-parallelized usage of the index:

ALTER INDEX <schema>.<index_name>
 NOPARALLEL;

Update regarding rebuilds of large partitioned tables with global indexes:

It is my experience that trying to run multiple rebuilds simultaniously, with a low degree of parallelism, or no parallelism at all, is a terrible idea. You will get nowhere and your sessions will create wait-events all over the place.

If you find yourself in a situation where you have to rebuild a number of large global non-partitioned indexes on a large partitioned table, it's better to throw all the CPU power you can find on the database server, and focus all CPU power on building one index at a time. You should run with an aggressive number of parallel slaves.

In my example, I had a virtual VMWare server with 38 cores and exclusive rights to the database.

The following statement was resonably effective:
ALTER INDEX myindx REBUILD PARALLEL 34 ONLINE NOLOGGING;
Then, when the index has been rebuilt, move to the next index and repeat procedure.

No comments:

Post a Comment