Speed up Index Creation Time

Creating an index on a large table can take a lot of time.  To minimize index creation time, you can use the below tips to speed up the creation of a large index.  
1)  Parallel index creation --
      example:  create index my_index1 on Product (prod_id, serial) parallel 5
      To disable parallel it:  alter index my_index1 noparallel;
2)  Nologging to skip writing entries to the redo log
      example:  create index my_index on Product (prod_id, serial) nologging tablespace data;
      Nologgin option not only increase the performance but also save spaces of the redo log files
3)  Compressing Index -- to avoid the duplication of key in a none-unique index by specifying compress        option on a non-unique index on a composite key
     example:  crate index my_idex1 on Product (catalog, type, Prod_id) compress 2;
4)  Put them all together
     Create index my_index1 on Prod(catalog, type, Prod_id) parallel 5 nologging compress 2;

No comments:

Post a Comment