Create index with parallel or nologging option

Creation of index with nologging or parallel option

During creation of index with nologging and parallel option, you will increase the speed of creating index process.
Because Nologging will not generate the redo logs for archive and parallel parameter use the multiple process to create the Index, which will increase the speed.
Note: Parallel option available in Enterprise edition.

Disable the log generation during creation of index:

create index inv_idx1 on inv(inv_id, inv_id2)
nologging tablespace inv_mgmt_index;

Increase the degree of parallelism for creating index on large table

create index inv_idx1 on inv(inv_id)
parallel 4 tablespace inv_mgmt_data;

Verify the index degree

select index_name,logging, degree from user_indexes;

Rebuild index with parallel and no logging mode:

alter index inv_idx1 rebuild parallel 4 nologging;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.