Challenging job : Index creation on very large table 450 Gb

[ashish_vikas]

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

[Former Member]

Hi,

Read SAP Note 682926 - Composite SAP note: Problems with "create/rebuild index"

Question -2 .

Regards,

[Former Member]

You can use 2 parallel process for each CPU. Read Note 338592 - SAPDBA: PARALLEL DEGREE for tables and indexes

You can update base tables at the same time you are building or rebuilding indexes on that table. You can also perform DML operations while the index build is taking place but only not allowed is the DDL operations.