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