Home > Oracle COREDB > How to delete huge data from a table.

How to delete huge data from a table.

June 10, 2013

Hi,

Recently I was asked to prepare the script to delete rows from a table which contains 700 millions. I have worked on couple of methods. Few of the solutions are as follows.

1) Table structure is as follows.

CREATE TABLE "GGUSER"."MYTEST"
 ( "SRNO" NUMBER,
 "MYDATE" DATE,
 CONSTRAINT "TESTCONTRAINT1" PRIMARY KEY ("SRNO")
) TABLESPACE "USERS";

2) Now insert huge data using following command.  I inserted around 7 millions.

BEGIN
    FOR i IN 1..7000000 LOOP
        INSERT INTO MYTEST values(i,SYSDATE-(i/10000));
        if mod(i,10000)=0 then
               COMMIT;
        end if;
    END LOOP;
END;
/

Now decide the method which you would like to choose.

Method:- 1 (Using cursor)
1. Create the following table
Create table TRK_TABLE (ID NUMBER);
Insert into TRK_TABLE values (0);
Commit;

2. Save the following script and run it

set time on
set feedback on
SET echo ON;
SET serveroutput ON SIZE 1000000
spool delete.log

DECLARE

delete_counter INTEGER;
commit_count NUMBER;
err_num number;
err_msg varchar2(200);
CURSOR TARGET_TABLE_id IS
select * from MYTEST where id > (SELECT ID FROM TRK_TABLE) and mydate < to_date(’22-MAY-2013′) order by ID;

TARGET_TABLE_id_rec TARGET_TABLE_id%ROWTYPE;

BEGIN

delete_counter:=0;
commit_count:=200;

FOR TARGET_TABLE_id_rec IN TARGET_TABLE_id
LOOP
delete FROM MYTEST where id = TARGET_TABLE_id_rec.id ;
delete_counter:= delete_counter+SQL%ROWCOUNT;
IF MOD(delete_counter, commit_count) = 0 THEN
Update TRK_TABLE set id = TARGET_TABLE_id_rec.id;
COMMIT;
DBMS_LOCK.SLEEP(0.5);
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Total count Deleted->’ || delete_counter || ‘: deleted on ->’ || sysdate);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; err_num := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
dbms_output.put_line(‘Error: ‘||err_num||’:: ‘||err_msg);

END;
/
spool off;

 Method2:-

Will update shortly.

Advertisements
Categories: Oracle COREDB
%d bloggers like this: