Optimizing Oracle index create with CMT based servers

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.
Comments:

Post a Comment:
Comments are closed for this entry.
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder