How can I update a huge Oracle table containing 4 billion records
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;
Software/Hardware used:
Software/Hardware used:
ASKED:
November 15, 2006 2:53 AM
UPDATED:
November 16, 2006 10:00 AM
Answer Wiki:
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);
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
