Faster way of creating a unique index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Faster way of creating a unique index

  1. #1
    Join Date
    Dec 1999
    Posts
    217

    Faster way of creating a unique index

    I am trying to create a unique index on a table that has 220994866 rows. In the create index if I add the parallel clause will that be faster? Also, if that is a more faster and efficient way please give me details on the exact syntax and also the pros and cons of using it.

    Thanks,
    Chintz

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Maybe yes, maybe no. Do you have multiple CPUs? Is your disk throughput adequate for n threads?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Is the table partitioned? That should make it easier to create an index in parallel. Also depending on your application, you should get better response times with a partitioned table.

    I would hope that you would have more than one CPU on your server.
    this space intentionally left blank

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    nologging and unrecoverable

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Truncate the table, then build the index.....


    well,.........at least it will be faster!

    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Originally posted by Mr.Hanky
    Truncate the table, then build the index.....


    well,.........at least it will be faster!

    Wow, you are really playing Mr. Helpful today.
    this space intentionally left blank

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by gandolf989
    ... may ... get better response times with a partitioned table.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Dec 1999
    Posts
    217
    Thanks for you replies.
    No, the table is not partitioned. Yes there are multiple CPUs on the server. Currently, I am creating the index with the parameters nologging, parallel 4.

    Chintz

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by gandolf989
    Wow, you are really playing Mr. Helpful today.
    Hey, if he listened to me he'd be done by now.


    Outside of parallel and...
    ALTER SESSION SET SORT_AREA_SIZE=???????

    I am out of suggestions.
    I remember when this place was cool.

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    I also think that increasing the sort_area_size to a MAXIMUM would help a lot. But don't set it to large, because with paralllel 4 your query will use up to 4 times the value set by sort_area_size.

    Also in case your DB is running with WORKAREA_SIZE_POLICY=AUTO you have to set it to MANUAL on session level, otherwise the parametre sort_area_size will have no impact.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width