How to update a oracle table containing 4 billion records

15 pts.
Tags:
Oracle
Oracle 10g
I want to update a column on the table containing 4 billion records. The table is partitioned and all partitions are compressed except the current month. I created the following procedure to do the update (I will do the update per month): create or replace procedure update_datetime_cdrdatanew is v_charging_date cdrdata_new.charging_date%type; v_timestamp cdrdata_new.timestamp%type; v_date_time_table cdrdata_new.date_time%type; v_seq_nr cdrdata_new.seq_nr%type; v_date_time timestamp; cursor cdr_update is select charging_date, timestamp, seq_nr from cdruser.cdrdata_new where charging_date between '01-jan-2004' and '31-jan-2004' for update; begin for cdr_rec in cdr_update loop v_date_time := to_date(to_char(to_date (cdr_rec.charging_date,'dd-mon-rr')||' '||cdr_rec.timestamp),'dd-mon-rrrr hh24miss'); update cdrdata_new set date_time = v_date_time where current of cdr_update; end loop; commit; end update_datetime_cdrdatanew; I made the specific partitnion writable again and started the procedure. After 4 hours I stopped the procedure, because it did not even update one month in 4 hours. The table hosts data for 4 years. I need to do this update for the entire 4 years. Any suggestions on how to speed up the update. Kind regards

Answer Wiki

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

To get the best possible speed, (1) use a single update instead of a cursor loop and (2) convert the timestamp to a fraction of a day, and add that to a date without TO_CHAR or TO_DATE conversion. (If you were to use such conversions, YYYY is better than RR, and numeric months are better than textual.)

To get restartability instead, (1) COMMIT after every thousand iterations, and (2) Add to the WHERE clause newField IS NULL.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 3  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
  • Dansken
    I agree that a direct SQL statement is the best way to do this. If you have to do something with the data before updating it I would suggest using bulk collects and forall updates. On the bulk collect you want to use the limit option so your array isn't to big. HTH, Magnus
    0 pointsBadges:
    report
  • Kojakhu
    Hi, You could also use a new table into which you insert the modified rows from the partition. Use CTAS and compression with the new table. Then you can exchange the new table (that is the updated rows) with the partition using an appropriate ALTER TABLE (see Oracle SQL Reference for details of the command). Then you can drop the exchanged table containing the old data. This way you could benefit from direct path writes during the CTAS. This way you don't generate UNDO (formerly ROLLBACK) data. Also you get your rows compressed. / And if you are running your database in noarchivelog mode (I hope this is not the case) than you can even save the redo this way. / This is just a thought, if you implement it, please let me know how it worked. / And of course avoid any unneccessary processing. / Regards, Kojak
    0 pointsBadges:
    report
  • Ws8oBPSQ
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/how-to-update-a-oracle-table-containing-4-billio... (0) Comments Read [...]
    0 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