Delete thousands of records from a table

Question

I have a table, for example, pessoa, and would like to delete all people over 40, with the script below do it:

DELETE FROM pessoa WHERE pessoa.idade > 40;

The problem there is more than 2 million record in the table, with people over 40, and as the bank is in production, while making this will lock all users.

There is a more efficient way to solve this problem?

Answer 1

You should proceed with parallel operations, if I had a good processor can run as follows:

ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ parallel(pessoa, 20) */
  FROM  pessoa
  WHERE idade > 40;

Follow the link from Oracle website where it talks about process parallelization, another good reference, and another reference to how to delete large amounts of information.

---

In my opinion, I work with very large banks with over 100 million records. A delete of these that you want to do should not take more than 2 or 3 minutes if your server is good.

Answer 2

Instead of you running delete to delete all records, you can create a PL and gradually delete and commit every x records, this will prevent you from "locking" other users.

declare
  cursor c is select p.rowid r from pessoa p where p.idade > 40;
  c_commit number:= 0;
  l_commit number:= 10000;
  begin
    for t in c 
    loop
      c_commit := c_commit + 1;
      delete from pessoa p where p.rowid = t.r;

      if mod( c_commit, l_commit ) = 0 then
        commit;
      end if;
    end loop;
    commit;
end;
/

Answer 3

I’m not an oracle expert, but you can also create a table with the records you want keep up.

Something like:

create table pessoa_new as select * from pessoa where idade <= 40;

After that, run a truncate in the table original:

truncate table pessoa

Note that truncate does not create log in oracle, that is, you will not can do rollback or commit. Once done, it cannot be undone.

After that, copy the data back to the original table:

alter session set rollback_segment = 'HUGE_RBS';   

insert into pessoa as select * from pessoa_new

Or if you prefer, change the name of the new and old table:

alter table pessoa rename to pessoa_old;
alter table pessoa_new rename to pessoa;

In either case, remember rebuild If you choose to rename tables, consider recreating constraints, triggers, etc.

Answer 4

The Aron Linhares solution works well, it would only add the use of BULK COLLECT to improve performance.

DECLARE
  CURSOR C_CURSOR IS SELECT P.ROWID AS CHAVE FROM PESSOA P WHERE P.IDADE > 40;
  TYPE TYPE_CURSOR IS TABLE OF C_CURSOR%ROWTYPE INDEX BY BINARY_INTEGER;
  R_CURSOR    TYPE_CURSOR;       
  BEGIN
    OPEN C_CURSOR;
      LOOP
      --preeche a memória de 1000 em 1000 registros
        FETCH C_CURSOR BULK COLLECT INTO R_CURSOR LIMIT 1000;
        EXIT WHEN R_CURSOR.COUNT = 0;
          FOR I IN 1 .. R_CURSOR.COUNT
          LOOP 
              DELETE FROM PESSOA P WHERE P.ROWID = R_CURSOR(I).CHAVE;
          END LOOP;
          --VAI APLICAR COMMIT DE 1000 EM 1000;
          COMMIT;
     END LOOP C_CURSOR;
    CLOSE C_CURSOR;
END;
/

In the documentation of Oracle, you will find more details on how to work with large data mass in PL/SQL.

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html