CODEWITHSUNDEEP
sqldatabaseoracle-database
armusk
armusk

Reputation: 83

I want to delete large amounts of data in oracle

I used this code but it takes too long, and the computer crashes:

begin
  for i In 0..1000
  loop
    delete from mytable WHERE user_date BETWEEN to_char(to_date('20120101000000','YYYYMMDD')+i,'YYYYMMDDHH24MISS') and to_char(to_date('20120101235959','YYYYMMDD')+i,'YYYYMMDDHH24MISS');
  end loop;
end;

I think I need to apply bulk SQL to this code.

I have to erase the data in the 2 year range

Please help me.

user_date type is char (20120101000000)

Upvotes: 0

Views: 176

Answers (2)

gsalem
gsalem

Reputation: 2028

Which version of Oracle are you using? If 19c, the you can do 

alter table mytable move including rows where user_date NOT BETWEEN '20120101000000' AND '20150101000000' online.

Upvotes: 0

wolφi
wolφi

Reputation: 8361

72.000.000 rows is a lot, so special measures are in order. I'd count how many rows will be deleted and how many will be kept with f.i.:

SELECT todo, count(*) FROM (
  SELECT CASE 
           WHEN user_date BETWEEN '20120101000000' AND '20150101000000' 
           THEN 'delete'
           ELSE 'keep'
         END AS todo
    FROM mytab
) GROUP by todo;

As @Littlefoot said, if a large enough amount is to be deleted, I'd try the CTAS approach:

CREATE TABLE mytemp NOLOGGING AS
SELECT * FROM mytable 
 WHERE user_date NOT BETWEEN '20120101000000' AND '20150101000000';

DROP TABLE mytable;
RENAME mytemp TO mytable;

Caution: You'll have to recreate all indexes, privileges, etc. Please try this on a test system preferably on a small example.

If, on the other hand, only a small percentage of rows will be deleted, and the rest will be kept, I'd follow @Tim Biegeleisen's approach:

CREATE INDEX i ON mytable(user_date);

DELETE FROM mytable
 WHERE user_date BETWEEN '20120101000000' AND '20150101000000';

If you follow the second approach, and the number of rows is still substantial, you'll might need to free up the space by reorganizing the table. Ask a DBA about it.

If you don't have a test system, you can create on by copying a small sample (say 0.1%) of your huge table:

CREATE TABLE mytest NOLOGGING AS SELECT * FROM mytable (SAMPLE 0.1);

I didn't fully understand your date logic, therefore used all dates from 2012-01-01 till 2014-12-31 in my examples.

Oh, and yes, @a_horse_with_no_name is totally right, never store dates in varchar.

Upvotes: 1

Related Questions