Fast Index Creation, Save your time!

 

 

 

 

 

 

One day you face to create a very big table index, the creation time nearly 5-6 hours, but can we do something to speed up this index creation in Oracle database? yes, we can. Lets see some tips that help to speed up the task
and save lot of time.

1- NOLOGGING
In a create index sentence will restrict the database from generating redo log, only minimal creation, that improve a lot the performance.

create index idx_classdoc on tabdoc(did,class) NOLOGGING;

2- COMPRESS
That enable key compression eliminating repeated occurrence of key column, that reduce storage.

create index idx_classdoc on tabdoc(did,class) NOLOGGING COMPRESS;
3- PARALLEL
The best for the end, this tip is the best will really help, if no worry about CPU, we can use PARALLEL. In a index creation there are step that process do a full-table scan. With parallel option this operation will do the table scan in paralle threads depending number of CPUs, the partiotioning of the table and disk configuration will reduce the index creation time.

create index idx_classdoc on tabdoc(did,class) NOLOGGING COMPRESS PARALLEL 3;

Advertisements

Comment

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

WordPress.com Logo

You are commenting using your WordPress.com 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