Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Best way to update huge table?

    Hi,

    I need to update table2.field_x with value from table1.field_x. Both tables have over 130 million rows. "Normal" update would look like this:

    UPDATE table2 SET field_x = (SELECT table1.field_x where table1.id = table2.id);

    But this is way too heavy in this case..

    br,
    Mika

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Update a table in several phases; it could be by date or by some other column, or even rowid range.

  3. #3
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

    use partitioning option

    when you have partitioning option, then insert data into partitioned table, and make exchange partition.
    when no, use cursor with rowid and commit each 10 000 - 500 000 rows.
    but check of speed mod function - try replace with some counter variable.
    and check, if on updated columns not are indexes
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ... commit each xxx rows
    Just make sure not to run into the SNAPSHOT TOO OLD error.

  5. #5
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    no problem... you can define exception for this error. but you need recognize updated records
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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