Challenging job : Index creation on very large table 450 Gb

Hello friends,

We have to create a Index on a table of size 450GB in Production system..

I already checked SAP Note 334224 and will do with SQl

Create the index using SQLPLUS with the following command:

create index <index name> on <table name> ( <field1, field2, ...> )

nologging tablespace <tablespace name>

parallel (degree <number>)

storage <storage clause>

..........online;

However, when i did it in test system, it took around 35 hours for same with table size 200 Gb. however we did it with transport and not with SQL.

I can use more parallel degree in production to reduce time..

how many should be used ??

Also, i want to understand, if for the full run say if it goes for 10 hours, complete table will be locked by database locks for insert update and delete operations ??

We have Oracle 10G

thanks

ashish

Edited by: ashish vikas on Nov 4, 2010 3:09 AM