Updation of Oracle tables

pts.
Tags:
Development
Tech support
Dear Sir/Madam I have a table t1 with 5 million records I have to update col2 of the table t1 as follows: update t1 set col2=1 where col2=a; update t1 set col2=2 where col2=b; update t1 set col2=3 where col2=c; update t1 set col2=4 where col2=d; update t1 set col2=5 where col2=e; and so on for 400 more values. Please let me know is there a better way of updating the col2 of t1 instead of firing so many update statements can I do it with a single update? if so then how and how will it affect the performance? Please note that 1,2,3 and a,b,c,.. can be any var cahr values. It would also be great if you can send the approximate performance numbers expected. would it be 1 min, 10 min, 1 hour etc for the above scenario. At present there is no index, Would indexing help in the update? Thanks and Regards Tanmoy

Answer Wiki

Thanks. We'll let you know when a new response is added.

As far as I know, indexing will degrade performance as indexes have also have to be updated with every update of the table.
For the best method, I’ll get back..

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • RickPrice
    Why not use a PL/SQL function or CASE statement to convert the values? You would then be able to run the update once. Rick
    0 pointsBadges:
    report
  • Mangeler
    use a cursor to loop through your selected records and update them as required. :)
    0 pointsBadges:
    report
  • Vikramranabhatt
    u can use stored procedure that will greatly improve the performance. u hav to use index to modify the col2 but if u use store procedure then it will help u
    0 pointsBadges:
    report
  • Randym
    A function that basically uses a case statement, If statement or maybe even an array would be efficent. Your statement would look something like this: Update t1 set col2 = setvalue(col2). Your function would look something like this: create or replace function setvalue (invalue in varchar2) return varchar2 as begin case invalue when '1' then return('a'); when '2' then return('b'); etc..... else return('?'); end case; end; The greatest amount of time would probably be spent creating the function. Indexing would make a great difference.
    1,740 pointsBadges:
    report
  • KShorting
    You could try using a decode statement as follows. update t1 set col2 = decode(col2,'a','1','b',2') But with 500 values, I know you will hit a maximum limit for the decode function. You will also have to code all 500 possibilities. You should be able to store the before and after values in a temporary table and update col2 based on the lookup as follows. update t1 set (col2) = (select temp.col2_new from temp where col2_old = t1.col2)
    0 pointsBadges:
    report
  • Mangeler
    I like KShorting's Idea with a temp table to map the values...
    0 pointsBadges:
    report
  • Randym
    The problem with the temp table idea is that you are going to be in effect executing 5 million (as originally stated number of records to update) select statements on that table. That may result in slow speed. The idea of a function would be quicker and you could write a program to build the function based on the temp table.
    1,740 pointsBadges:
    report
  • Randym
    The problem with the temp table idea is that with orginally stated number of records of 5 million, you will be in effect executing 5 million select statements. That could make it slower than a function.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following