Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: Which Option Performs Best

    I need to update 20 columns of a 250 million row table (table a) with columns from a 20 million row table (table b). Both tables have the same primary key (e.g. promo_id).

    With performance in mind, I am thinking of two options.

    1) Loop thru cursor of table b. For each cursor row, update table a where a.promo_id = promo_id

    2) Loop thru cursor of table a joined with table b selecting only necessary columns with for update of clause. For each cursor row, update table a where current of cursor.

    Which option will perform faster? Or is there another option that I am missing?

    Thanks for your input.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Or is there another option that I am missing?
    A single UPDATE statement would be MUCH more efficient.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Anacedent is right, you should use a single SQL as often as you can, because the operation will be optimized as a whole. Looping through cursors is just doing manually "one very row at a time" what the DB can do in a single operation on ALL the affected rows. Remember, a DBMS is made to perform SQL (and Oracle excels at it !). Now what you can wonder is if you would need another index so as to access these huge data efficiently... if you only compare on the PK to know which rows to update, then you don't need other ones.

    BTW, if you have to update 250 million rows, the UPDATE will be long, but your cursor loop solution would have lasted for ages ! Be sure to have a big enough UNDO tablespace, because you'll need it ! (otherwise the UPDATE will fail)

    HTH & Regards,

    RBARAER

  4. #4
    Join Date
    Mar 2005
    Posts
    3
    Quote Originally Posted by RBARAER
    Anacedent is right, you should use a single SQL as often as you can, because the operation will be optimized as a whole. Looping through cursors is just doing manually "one very row at a time" what the DB can do in a single operation on ALL the affected rows. Remember, a DBMS is made to perform SQL (and Oracle excels at it !). Now what you can wonder is if you would need another index so as to access these huge data efficiently... if you only compare on the PK to know which rows to update, then you don't need other ones.

    BTW, if you have to update 250 million rows, the UPDATE will be long, but your cursor loop solution would have lasted for ages ! Be sure to have a big enough UNDO tablespace, because you'll need it ! (otherwise the UPDATE will fail)

    HTH & Regards,

    RBARAER
    RBARAER

    Thanks for the input. What would be the syntax for the update? Would I have to have one select per column as in below?

    update table_a a
    set a.column1 = (select b.column1 from table_b b where b.promo = a.promo)
    .......
    set a.column20 = (select b.column20 from table_b where b.promo = a.promo);

    I appreciate your input as well as ANACEDENT's.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    update table_a a
    set (a.column1,
           a.column2,
           a.column3...) = (select b.column1,
                                    b.column2,
                                    b.column3...
                                  from table_b b 
                                  where b.promo = a.promo)
    where exists (select null
                       from table_b c
                       where c.promo = a.promo);
    The update assumes that the promo code is unique in table_b
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2005
    Posts
    3
    beilstwh,

    Thanks. It works.

Posting Permissions

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