CREATE INDEX PARALLEL NOLOGGING

Index create speed:  performance factors

Parallel option ? This option allows for parallel processes to scan the table.  When an index is created, Oracle must first collect the symbolic key/ROWID pairs with a full-table scan.  By making the full-table scan run in parallel, the index creation will run many times faster, depending on the number of CPUs, table partitioning and disk configuration.  I recommend a n-1 for the degreeoption, where n is the number of CPUs on your Oracle server.  In this example we create an index on a 36 CPU server and the index create twenty times faster:

CREATE INDEX cust_dup_idx

   ON customer(sex, hair_color, customer_id)

PARALLEL 35;

Nologging option ? The nologging option bypasses the writing of the redo log, significantly improving performance.  The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery.  Using nologging with create index can speed index creation by up to 30%

CREATE INDEX cust_dup_idx

   ON customer(sex, hair_color, customer_id)

PARALLEL 35

NOLOGGING;