Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Nov 04, 2010 at 09:48 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 04, 2010 at 09:59 AM

    Hi,

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

    Question -2 .

    Regards,

    Add comment
    10|10000 characters needed characters exceeded