Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How can I quickly delete all records from a big table?
Cristian Veronesi <c.veronesi_at_crpa.it> schrieb in im Newsbeitrag:
39925E73.C1097102_at_crpa.it...
What is the quickest way to empty a big table (200000 records) without
encountering rollback segment problems? TRUNCATE TABLE does not work
because the table has children (but the children are empty!!!). I made a
PL/SQL script like this :
begin
for rec in (
select pkey from bigtable
) loop
delete from bigtable where pkey = rec.pkey ;
commit ;
end loop ;
end ;
Truncate is the best way, you must disable integrity constraintr to the tables. If you want to delete from a loop try this one, this is a little bit faster:
loop
delete from bigtable where rownum < upper_bound_value;
exit when SQL%NOTFOUND;
commit;
end_loop;
and set upper_bound_value e.g. to 10000, then each at transaction 10000 records are deleted from the table.
Mike Received on Thu Aug 10 2000 - 00:00:00 CDT