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.
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;
That enable key compression eliminating repeated occurrence of key column, that reduce storage.
create index idx_classdoc on tabdoc(did,class) NOLOGGING COMPRESS;
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;