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

Oracle: disabilitare/abilitare tutti i constraints su una tabella

Problema: si devono disabilitare e successivamente riabilitare tutti i constraints su una tabella (senza disabilitarli ad uno ad uno!)

Soluzione: recuperare i nomi dei constraints dalle tabelle di sistema ed effettuare le alter table sfruttando un ciclo. (Fonte Stackoverflowhttp://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle)

Disable all constraints:

BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
AND t.table_name = 'MY_TABLE_NAME'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
END LOOP;
END;
/

Enabling the constraints again:

BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'DISABLED'
AND t.table_name =  'MY_TABLE_NAME'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
END LOOP;
END;
/

(eliminando il filtro sul nome della tabella si può effettuare l’operazione su tutte le tabelle dello schema)

Oracle 10g: Truncate before drop. Any real improvement?

Su Oracle, effettuare una TRUNCATE prima del DROP è effettivamente conveniente?(in velocità e consumo di risorse)

Comportamento di TRUNCATE e DROP:

  • TRUNCATE:
    • TRUNCATE removes all rows from a table
    •  TRUNCATE statement is a DLL command and cannot be rolled back  and no triggers will be fired=> generates no rollback data
    • On a clustered table, the data must be removed from the entire cluster, not just the one table. Any referential integrity constraints on a table must be disabled before it can be truncated.
    • The database does not actually delete any rows with the Oracle TRUNCATE statement, but with the Oracle TRUNCATE resets the table definition to an empty table by resetting the high water mark. As the truncated table begins to grow, the space where the old rows were written will be overwritten with the new rows.
  • DROP
    • The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed.
    • DROP statement is a DLL command, No DML triggers will be fired. The operation cannot be rolled back => generates no rollback data
    • Oracle 10g introduced the recycle bin. You can recover a table that you have dropped from the Oracle recycle bin by using the flashback table command.
What about e High Water Mark?
The High Water Mark is the maximum number of blocks which have ever contained data. Deleting records from a table frees up the space but does not move the HWM. In order to reset the High 
Water Mark we have to drop and recreate the table, or just truncate it.
Normally we wouldn't bother. However, the High Water Mark defines the range of a full table
scan. So if we have a table which once contained a lot of records but no longer does so and
also is subject to frequent full table scans then perhaps reseting the HWM would be a good use of our time.

Ma se la tabella deve essere cancellata, non ha senso preoccuparsi dell'HWM.

In definitiva, non ci sono vantaggi ad effettuare una TRUNCATE prima della DROP, perché in effetti le due operazioni hanno le stesse caratteristiche in termini di consumo di risorse. L’unica differenza (dalla versione 10g) è il recycle bin, che occupa effettivamente spazio utile, per by-passarlo basta aggiungere l’opzione PURGE alla DROP:

drop table tbl_rc purge;

drop_table

Riferimenti:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:595970200346389228
http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands
http://www.dba-oracle.com/t_oracle_recycle_bin.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:669044239081
http://chandu208.blogspot.it/2012/02/high-water-mark-hwm.html

Data Warehouse

I sistemi di Data Warehouse rientrano nei sistemi di supporto alle decisioni, il loro obiettivo è quello di rendere disponibile una analisi dei dati che sia di supporto alle decisioni aziendali.

Un esempi di Architettura DW a 3 Livelli:

  • Dati provenienti dai DB operazionali o da altre fonti
  • Uno schema “di riconciliazione”, dove appoggiare i dati che andranno inseriti nel DW
  • Uno schema DW composto a sua volta da diversi DataMart

Università “La Sapienza” di Roma,Sistemi Informativi Aziendali,
Prof. Umberto Nanni

Tramite ETL (Extraction, Transformation, Loading) i dati vengono portati dai sistemi sorgenti (DB operazionali, fonti esterne) allo schema DW.

See more at:
http://www.di.unipi.it/~giangi/CORSI/SISD/Lezioni/SISD2.pdf
http://www.dis.uniroma1.it/~nanni/Didattica/MatDid/SIA/slides/SIA_Nanni_6_IntroDW.pdf