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:
- Usare le partizioni: il modo più veloce di effettuare una delete massiva è droppare una partizione.
- Usare la bulk delete: la bulk delete spesso è più veloce della standard SQL delete.
- 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.
- 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)
- Parallelizzare il job di delete: usando la hit parallel si possono parallelizzare le delete.
- 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).
- 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.
- 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
appuntinerd 