How to delete ‘millions of rows’ without creating rollback (Doc ID 117954.1) | To Bottom |
Modified:Nov 20, 2002Type:BULLETINStatus:PUBLISHEDPriority:3 |
|
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:
- 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
- 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;
/
- 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.