How to update a oracle table containing 4 billion records
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
Software/Hardware used:
Software/Hardware used:
ASKED:
November 15, 2006 3:03 AM
UPDATED:
November 16, 2006 12:51 PM
Answer Wiki:
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)
To see all answers submitted to the Answer Wiki: View Answer History.
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
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