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;


2 thoughts on “How to make an Index creation Faster

  1. It’s really a cool and useful piece of information.
    I am happy that you simply shared this helpful info with us.
    Please stay us informed like this. Thanks for sharing.


  2. You actually make it seem so easy with your presentation but I find this matter to be really
    something that I think I would never understand.
    It seems too complicated and very broad for me. I am looking forward
    for your next post, I’ll try to get the hang of it!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s