Optimizing Oracle index create with CMT based servers
By glennf on May 21, 2008
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.
This simply uses parallel query/dml to speed the creation of indexes.
create index gtest_c1 on gtest(idname) pctfree 30 parallel 64 tablespace glennf_i unrecoverable;
ResultsThe 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.