oracle delete millions of rows without any rollback




How to delete ‘millions of rows’ without creating rollback (Doc ID 117954.1) To Bottom
Modified:Nov 20, 2002Type:BULLETINStatus:PUBLISHEDPriority:3
Comments (0)

 

Purpose:

 

If you have a table with millions of rows and you want to delete e.g. 100’000 of them, all changes will be noticed in your rollback segment. So it can be, that one ‘delete’ – this means one transaction! – cannot be hold in your rollback segment. The result is a broken job and ORACLE feedback in an ORA error message.  To resolve this, a small PL/SQL procedure with dynamic SQL is generated.

 

Solution:

 

  1. To make a good demonstration I create a table big_emp derived from SCOTT.EMP

create table big_emp as select * from emp.

Execute several times insert into big_emp select * from big_emp; gives me a table with 1’114’112 rows.

=> only 11 distinct rows exists

 

  1. Create PL/SQL procedure:

 

CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2,

empno IN NUMBER  ,

nrows IN NUMBER    ) IS

 

sSQL1  VARCHAR2(2000);

sSQL2  VARCHAR2(2000);

nCount NUMBER;

 

BEGIN

 

nCount := 0;

sSQL1:=’delete from ‘|| tablename ||

‘ where ROWNUM < ‘ || nrows || ‘ and empno=’ || empno;

sSQL2:=’select count(ROWID) from ‘ || tablename ||

‘ where empno= ‘ || empno;

 

LOOP

 

EXECUTE IMMEDIATE sSQL1;

 

EXECUTE IMMEDIATE sSQL2 INTO nCount;

 

DBMS_OUTPUT.PUT_LINE(‘Existing records: ‘ || to_char(ncount) );

 

commit;

 

EXIT WHEN nCount = 0;

 

END LOOP;

 

END delete_tab;

/

 

  1. Execute above created procedure

 

SQL> execute delete_tab(‘big_emp’,7369,5000)

Existing records: 60537

Existing records: 55538

Existing records: 50539

Existing records: 45540

Existing records: 40541

Existing records: 35542

Existing records: 30543

Existing records: 25544

Existing records: 20545

Existing records: 15546

Existing records: 10547

Existing records: 5548

Existing records: 549

Existing records: 0

 

PL/SQL procedure successfully completed.

 

 

Conclusion:

 

Normally deleting just one employee from big_emp generates more than 10-Megabyte Rollback for ca. 100’000 deleted rows. In opposite of  e.g. half of  additional rollback using delete_tab function. With this function you adjust the size of rollback segments created by a ‘delete’ statement. Another advantage is, that not one rollback segment is used. After a commit the next rollback segment will fit the next transaction, so the load is distributed about all rollback segments.

 

 

Author: admin