Optimizing Oracle index create with CMT based servers

Wednesday May 21, 2008

One of the most common ways to improve SQL performance is the use of indexes. While Oracle does have a wide variety of indexes available, these tests focus on the most commonly used B-tree index. On large tables it is important to ensure indexes get created in a timely fashion, that is why Oracle introduced several features to decrease index creation time:

• "unrecoverable"
  
  This feature prevent the logging of intermediate steps of the index creation process. There is really no value to logging of intermediate steps. Index creation should be thought of as an atomic process - if it fails, you can always start over. If you create indexes as "unrecoverable" they won't be recoverable until a backup is performed on the target tablespace.
  
  
• "parallel"
  
  This simply uses parallel query/dml to speed the creation of indexes.
  

The following index create statement shows how to use the "parallel" and "unrecoverable" features for index creation.

create index gtest_c1 on gtest(idname)
pctfree 30  parallel 64 tablespace glennf_i unrecoverable;

Results

The following test created an non-unique index on varchar(32) column of a 20GB table. Parallelism was increased from 1->64 in order to use the available IO bandwidth. With parallelism of 1 index creation took 34 minutes, while with parallelism of 64 it took only 3 minutes and 45 seconds!



These tests use the same configuration as previous posts regarding Oracle in the Throughput Computing series.