How to increase index creation speed

Suppose you’re creating an index based on a table that contains millions of rows. You want to create the index as fast as possible.

So here is the solution for increasing the speed of index creation:

• Turning off redo generation

• Increasing the degree of parallelism

You can use the prior two features independently of each other, or they can be used togather.

Turning Off Redo Generation

You can optionally create an index with the NOLOGGING clause. Doing so has these implications:

• The redo isn’t generated that would be required to recover the index in the event of a media failure.

• Subsequent direct-path operations also won’t generate the redo required to recover the index information in the event of a media failure.

Here’s an example of creating an index with the NOLOGGING clause:

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

You can run this query to determine whether an index has been created with NOLOGGING:

SQL> select index_name, logging from user_indexes;

Increasing the Degree of Parallelism

In large database environments where you’re attempting to create an index on a table that is populated with many rows, you may be able to reduce the time it takes to create the index by using the PARALLEL clause. For example, this sets the degree of parallelism to 2 when creating the index:

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

You can verify the degree of parallelism on an index via this query:

SQL> select index_name, degreel from user_indexes;

How It Works

The main advantage of NOLOGGING is that when you create the index, a minimal amount of redo information is generated, which can have significant performance implications when creating a large index. The disadvantage is that if you experience a media failure soon after the index is created (or have records inserted via a direct-path operation), and subsequently have a failure that causes you to restorefrom a backup (taken prior to the index creation), then you may see this error when the index is accessed:

ORA-01578: ORACLE data block corrupted (file # 4, block # 11407)
ORA-01110: data file 4: '/ora01/dbfile/O11R2/inv_mgmt_index01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

This error indicates that the index is logically corrupt. In this scenario, you must re-create or rebuild the index before it’s usable. In most scenarios, it’s acceptable to use the NOLOGGING clause when creating an index, because the index can be re-created or rebuilt without affecting the table on which the index is based.

In addition to NOLOGGING, you can use the PARALLEL clause to increase the speed of an index creation. For large indexes, this can significantly decrease the time required to create an index.

Keep in mind that you can use NOLOGGING in combination with PARALLEL. This next example rebuilds an index in parallel while generating a minimal amount of redo:

SQL> alter index inv_idx1 rebuild parallel nologging;