CREATE INDEX In Oracle - In Most Time Efficient Way

CREATE INDEX In Oracle - In Most Time Efficient Way

Create index in shortest time: In a production support environment main moto is to save time. There will be always fire on the floor.
In production databases indexes are of huge size. Creation of index normally takes a lot of time. If we create index in following way it will take half of the usual time.



02:02:30 SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newnpp1

Elapsed: 00:00:00.00

Step-1:

Create index as unusable:

It will get created in fraction of second.

02:06:26 SQL> CREATE INDEX NPP.NU_E_V_M_ID ON NPP.UW_PHON_VERSIONS(ENTITY_INDICATOR, PHON_VERSION_MASTER_ID) tablespace POS_INDX02 unusable;

Index created.

Elapsed: 00:00:00.03

Step-2:

Rebuild the index online in parallel:

Parallelism of rebuilding activity will speed it up. 

03:07:26 SQL> alter index NPP.NU_E_V_M_ID rebuild online parallel 8;

Index altered.

Elapsed: 01:33:59.90

Step-3:

Make the index noparallel.

05:01:13 SQL> alter index NPP.NU_E_V_M_ID noparallel;

Index altered.

Elapsed: 00:00:00.01

Step-4:

Gather statistics of the table on which the index is created upon.

05:02:55 SQL> exec dbms_stats.gather_table_stats('NPP', 'UW_PHON_VERSIONS',degree=>6,cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:09:37.08