Oracle 10g+: cancellare dati da tabelle con molti record

Per prima cosa, con *molti record* si intendono tabelle contenenti diversi milioni di record (per dare un numero, > 5 milioni).

Problema: ripulire tabelle da dati vecchi, quindi occorre cancellare tutti i record con data < data definita.

Soluzione: non c’è una soluzione univoca. Si possono seguire diversi approcci per cancellare i record:

  1. Usare le partizioni: il modo più veloce di effettuare una delete massiva è droppare una partizione.
  2. Usare la  bulk delete:  la bulk delete spesso è più veloce della standard SQL delete.
  3. Drop indexes & constraints: se la cancellazione può essere effettuata a sistema fermo, si può considerare di droppare gli indici e ricrearli una volta completata la cancellazione.
  4. Small pctused:  For tuning mass deletes you can reduce freelist overhead by setting Oracle to only re-add a block to the freelists when the block is dead empty by setting a low value for pctused. (non mi è chiara questa opzione)
  5. Parallelizzare il job di delete: usando la hit parallel si possono parallelizzare le delete.
  6. Usare NOARCHIVELOG dopo aver effettuato un full backup, settare il database in NOLOGGING mode per la delete, riportandolo dopo in ARCHIVELOG mode.(questo dipende dalla configurazione del db, in alcuni casi potrebbe già essere settato in NOLOGGING).
  7. Usare CTAS + DROP:  creare una nuova tabella usando CTAS con una select che prenda solo i record che si intende conservare. Quindi droppare la tabella di origine, rinominare la tabella di appoggio e ricreare constraints e indexes.
  8. Usare CTAS + TRUNCATE: creare una nuova tabella usando CTAS con la select che prenda solo i record che si intende conservare. Lanciare TRUNCATE sulla tabella di origine, effettuare una INSERT…SELECT in modalità NOLOGGING dalla tabella di appoggio alla tabella di origine.

Prova sul campo: ho scelto di sperimentare i metodi 7 e 8.

La tabella da ripulire che ho usato ha: record totali= 8.339.573, record che devono rimanere=637.158

Implementazione metodo 7: (xxxx è la tabella da ripulire)

(pseudocode)
procedure pi_storicizza_xxxx (IN pi_data)
  create table tmp_xxxx nologging 
    as select * from xxxx where data < pi_data;
  <commenti sulle colonne di tmp_xxxx>
  drop table xxxx purge;
  alter table tmp_xxxx rename to xxxx;
  <index>
  <constraint>

La procedura vera e propria:

create or replace PROCEDURE P_STOR_AM_RISULTATO_MOVIMENTO 
(
 PI_DATA IN VARCHAR2 
) AS
v_statement VARCHAR2(4000);
v_count_rec_to_save NUMBER := 0;
v_count_rec_tmp NUMBER := 0;
BEGIN
 select COUNT(*)
 INTO v_count_rec_to_save
 from
 am_risultato_movimento
 where dtmins >= to_date(PI_DATA,'dd-mon-yyyy');

 -- creo la tabella
 v_statement := 'CREATE TABLE TMP_AM_RISULTATO_MOVIMENTO NOLOGGING
 AS select * from am_risultato_movimento where dtmins >= to_date(' || '''' || PI_DATA || '''' || ',''dd-mon-yyyy'')' ; 
 EXECUTE IMMEDIATE v_statement; 

 -- commenti sulle colonne
 -- v_statement := '';
 -- EXECUTE IMMEDIATE v_statement;

 -- controllo se il numero di record da mantenere corrisponde 
 v_statement := 'select count(*) from TMP_AM_RISULTATO_MOVIMENTO';
 EXECUTE IMMEDIATE v_statement INTO v_count_rec_tmp;

 -- se si vado avanti
 IF (v_count_rec_to_save = v_count_rec_tmp) THEN
 -- drop tabella da storicizzare
 v_statement := 'drop table AM_RISULTATO_MOVIMENTO CASCADE CONSTRAINTS purge';
 EXECUTE IMMEDIATE v_statement; 

 -- rinomino la tabella tmp
 v_statement := 'ALTER TABLE TMP_AM_RISULTATO_MOVIMENTO RENAME TO AM_RISULTATO_MOVIMENTO';
 EXECUTE IMMEDIATE v_statement; 

 -- creo indici e vincoli sulla tabella 
 v_statement := 'CREATE INDEX IX_AM_RISULTATO_AM_MOVIMEN1 ON AM_RISULTATO_MOVIMENTO (PRGMOVIMENTOAPP) ';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'CREATE INDEX IX_AM_RISULTATO_AM_VALIDAZIO ON AM_RISULTATO_MOVIMENTO (PRGVALIDAZIONEMASSIVA) ';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'CREATE INDEX IX_AM_RISULTATO_TS_UTENTE ON AM_RISULTATO_MOVIMENTO (CDNUTINS) ';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'CREATE UNIQUE INDEX PK_AM_RISULTATO_MOVIMENTO ON AM_RISULTATO_MOVIMENTO (PRGRISULTATO) ';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'CREATE INDEX IX_AM_RISULTATO_AM_MOVIMENTO ON AM_RISULTATO_MOVIMENTO (PRGMOVIMENTO)';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'CREATE INDEX AM_RISULTATO_DTMINS ON AM_RISULTATO_MOVIMENTO (TRUNC(DTMINS)) ';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'ALTER TABLE AM_RISULTATO_MOVIMENTO ADD CONSTRAINT PK_AM_RISULTATO_MOVIMENTO PRIMARY KEY (PRGRISULTATO)';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'ALTER TABLE AM_RISULTATO_MOVIMENTO ADD CONSTRAINT FK_AM_RISULTATO_AM_MOVIMENTO FOREIGN KEY (PRGMOVIMENTO)
 REFERENCES AM_MOVIMENTO (PRGMOVIMENTO) ENABLE';
 EXECUTE IMMEDIATE v_statement; 

 v_statement := 'ALTER TABLE AM_RISULTATO_MOVIMENTO ADD CONSTRAINT FK_AM_RISULTATO_TS_UTENTE FOREIGN KEY (CDNUTINS)
 REFERENCES TS_UTENTE (CDNUT) ENABLE';
 EXECUTE IMMEDIATE v_statement; 

 ELSE
 DBMS_OUTPUT.put_line('Si e verificato un problema nella creazione della tabella TMP_AM_RISULTATO_MOVIMENTO. Storicizzazione AM_RISULTATO_MOVIMENTO interrotta');
 END IF;

END P_STOR_AM_RISULTATO_MOVIMENTO;

tempo di esecuzione: circa 13 minuti

Implementazione metodo 8:

(pseudocode)
procedure pi_storicizza_xxxx (IN pi_data)
  create table tmp_xxxx nologging 
    as select * from xxxx where data < pi_data;
  truncate table xxxx;
  <disabilito constraint&index>
  insert /*+ append */ into xxxx select * from tmp_xxxx;
  <riabilito constraint&index>
  drop table xxxx purge;

La procedura vera e propria:

create or replace 
PROCEDURE P_STOR_AM_RISULTATO_MOV_NEW
(
 PI_DATA IN VARCHAR2 
) AS
v_statement VARCHAR2(4000);
v_count_rec_to_save NUMBER := 0;
v_count_rec_tmp NUMBER := 0;
BEGIN
 select COUNT(*)
 INTO v_count_rec_to_save
 from
 am_risultato_movimento
 where dtmins >= to_date(PI_DATA,'dd-mon-yyyy');

 -- creo la tabella
 v_statement := 'CREATE TABLE TMP_AM_RISULTATO_MOVIMENTO NOLOGGING
 AS select * from am_risultato_movimento where dtmins >= to_date(' || '''' || PI_DATA || '''' || ',''dd-mon-yyyy'')' ; 
 EXECUTE IMMEDIATE v_statement; 

 -- controllo se il numero di record da mantenere corrisponde 
 v_statement := 'select count(*) from TMP_AM_RISULTATO_MOVIMENTO';
 EXECUTE IMMEDIATE v_statement INTO v_count_rec_tmp;

 -- se si vado avanti
 IF (v_count_rec_to_save = v_count_rec_tmp) THEN
 -- truncate tabella da storicizzare
 v_statement := 'TRUNCATE table AM_RISULTATO_MOVIMENTO';
 EXECUTE IMMEDIATE v_statement; 

 -- disable constraint
 P_DISABLE_ALL_TABLE_CONSTRAINT(PI_TABLE_NAME => 'AM_RISULTATO_MOVIMENTO');

 -- rimetto nella tabella solo i dati che mi interessa mantenere
 v_statement := 'insert /*+ append */ into AM_RISULTATO_MOVIMENTO select * from TMP_AM_RISULTATO_MOVIMENTO';
 EXECUTE IMMEDIATE v_statement; 

 -- ri-enable constraint
 P_ENABLE_ALL_TABLE_CONSTRAINT(PI_TABLE_NAME => 'AM_RISULTATO_MOVIMENTO');

 -- drop della tabella temporanea 
 v_statement := 'DROP TABLE TMP_AM_RISULTATO_MOVIMENTO PURGE';
 EXECUTE IMMEDIATE v_statement; 

 ELSE
 DBMS_OUTPUT.put_line('Si e verificato un problema nella creazione della tabella TMP_AM_RISULTATO_MOVIMENTO. Storicizzazione AM_RISULTATO_MOVIMENTO interrotta');
 END IF;

END P_STOR_AM_RISULTATO_MOV_NEW;

tempo di esecuzione: circa 18 minuti.

La scelta è ricaduta sul metodo 8, anche se il tempo di esecuzione è leggermente superiore rispetto al 7, è possibile parametrizzare il nome della tabella da pulire, in modo da avere una sola procedura e non una procedura per tabella (nel mio caso le tabelle da pulire sono  una decina).

Riferimenti:
http://www.dba-oracle.com/t_deleting_large_number_of_rows_in_oracle_quickly.htm
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2345591157689
http://www.dba-oracle.com/t_oracle_fastest_delete_from_large_table.htm
http://stackoverflow.com/questions/10092407/optimal-way-to-delete-specified-rows-from-oracle
http://stackoverflow.com/questions/644975/deleting-a-lot-of-data-in-oracle
http://www.dba-oracle.com/t_nologging_append.htm
Annunci