Make Index Creation Faster.

At the time of index creation full table scan are performed. Oracle fetch all rows from data file to memory and sort them prior to create index, to complete this task oracle need large memory for large table, when memory are not enough oracle will divide the data into smaller part, sort each part individually and then merge together the result. To complete all this task index creation will be slower.

Faster Index Creation :

SORT_AREA_SIZE: Value of this parameter (in byte) are used from memory to short data.

PARALLEL: At the time of index creation oracle collect collect ROWID from table with full table scan. With parallel option supplied in index clause Oracle will scan full table using the number of CPU's meson in parallel clause.

NOLOGGING: With NOLOGGING option provide Oracle will generate minimal redo, for that Index creation will be faster.

SQL> conn SALES/PASS@SALESDB
Connected.
SQL> show parameter SORT_AREA_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536
SQL>
SQL>  alter session set SORT_AREA_SIZE=700000;

Session altered.

SQL> CREATE INDEX IND_ORDER_LIST ON ORDERS(ORDER_ID) PARALLEL 16 NOLOGGING TABLESPACE USERS;

Index created.

SQL>