How to make an Index creation Faster

When an index is created on a table , a full table scan is
performed. Oracle fetches rows from the table into memory and sorts them prior  to creating the index.  For this task, Oracle requires sort area space in memory.  If memory areas for sorting are not large enough, Oracle will divide the data into smaller sections, sort each section individually, and then merge  together the results.  This is not as efficient as if memory allocated were large enough for the sort.

1) Increase "SORT_AREA_SIZE" parameter in the "init.ora".

 Sqlplus> alter session set sort_area_size=25000;


2) PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will

scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster.

On a server that have 6 CPUs you may give parallel 5 as below.
create index emp_idx on emp(id,code) parallel 5;

3) NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance.

create index emp_idx on emp(id,code) parallel 5 nologging;

 4) Larger Block Size: You can create an index in a tablespace that uses Larger block size. If you have DSS environment then you can do it. This will improve performance while creating index.

You can do it by first creating a tablespace with 32k blocksize and then create index under it,

create tablespace index_ts
datafile '/u01/index_file.dbf' size 1024m
blocksize 32k;

create index emp_idx on emp(id,code) parallel 5 nologging  tablespace index_ts;

Tag: Oracle, Rebuild Index, Create Index