How can I update a huge Oracle table containing 4 billion records

15 pts.
Tags:
Oracle
Oracle 10g
I want to update the table containing 4 billion records. The table is partitioned and all the partitions are compressed except for the current month. I added 1 column to the table and must update the column with a value. I created a procedure that must do the update. Procedure: 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' --and timestamp = '000650' for update; begin /* if cdr_update%isopen then close cdr_update; else open cdr_update; end if;*/ for cdr_rec in cdr_update loop --fetch cdr_update into v_charging_date, v_timestamp, v_seq_nr; --exit when cdr_update%notfound; --v_count := v_count + 1; /*if v_count = 10000 then v_count := 0; commit; --exit; end if;*/ --v_date_time := to_date(to_char(to_date(v_charging_date,'dd-mon-rr')||' '||v_timestamp),'dd-mon-rrrr hh24miss'); v_date_time := to_date(to_char(to_date(cdr_rec.charging_date,'dd-mon-rr')||' '||cdr_rec.timestamp),'dd-mon-rrrr hh24miss'); --dbms_output.put_line(to_char('date time datetime value: '||cdr_rec.charging_date||' '||cdr_rec.timestamp||' '||v_date_time)); update cdrdata_new set date_time = v_date_time where current of cdr_update; --where charging_date = v_charging_date --and timestamp = v_timestamp --and seq_nr = v_seq_nr; end loop; commit; end update_datetime_cdrdatanew;

Answer Wiki

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

You can use the following Merge Statment to update the table, the for loop will take huge resources.

The INSERT INTO Clause is optional if you are using
Oracle 10g and the Merge Statment only work with versions above 9i.

MERGE INTO cdrdata_new a
USING
(SELECT rowid, to_date(to_char(to_date(cdr_rec.charging_date,’dd-mon-rr’)||’ ‘||cdr_rec.timestamp),’dd-mon-rrrr hh24miss’) ntime
FROM cdrdata_new
) b
ON (a.rowid = b.rowid)
WHEN MATCHED THEN
UPDATE SET a.date_time = ntime;
WHEN NOT MATCHED THEN
INSERT INTO cdrdata_new(seq_nr, charging_date) VALUES(null, null);

Discuss This Question: 1  Reply

 
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
  • Ws8oBPSQ
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/how-can-i-update-a-huge-oracle-table-containing-... (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