T-SQL Table Variables with Dynamic SQL

All’interno di Store Procedure, può essere utile avere delle tabelle di appoggio in memoria. SQL Server mette a disposizione le Table Variable, variabili “tabella” che memorizzano record:

DECLARE @MyTable TABLE
(
 ProductID int UNIQUE,
 Price money CHECK(Price < 10.0)
)

(1) E’ necessario dichiarare la tabella all’interno dell’ SQL dinamico:Vanno seguite delle accortezze quando si combina l’uso di Table Variable e Dynamic Sql:

set @SQLQuery = '
  DECLARE @ProductTotals TABLE (   ProductID int,   Revenue money )
  INSERT INTO @ProductTotals (ProductID , Revenue ) 
  select ID, Revenue from myRealTable
  UPDATE OtherRealTable 
  set [Tipo Articolo]=tipo 
  from @ProductTotals  where OtherRealTable.ID > [@ProductTotals].ProductID '
 if @debug=1 
  print @SQLQuery 
else 
 exec sp_executesql @SQLQuery

(2) Quando si usa la Table Variable in una clausola di selezione attenzione alla sintassi da usare:

  1. OK: delete from @ProductTotals     where ProductID = 1
    OK: delete from @ProductTotals     where [@ProductTotals].ProductID = 1 
    KO: delete from @ProductTotals     where @ProductTotals.ProductID = 1 

 

Annunci

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

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: Mondrian – JPivot – MDX

Pentaho Mondrian Analysis è un motore per On Line Analytical Processing (OLAP) sviluppato in Java.

Visione generale di Mondrian

Per visualizzare i risultati delle query MDX, si può combinare Mondrian con JPivot o con altri tool di visualizzazione.

JPivot è una libreria di <tag> personalizzati che permette all’utente di visualizzare e 
navigare dati per analisi OLAP. I tag vengono definiti all’interno di Java Server Faces (JSP).

Architettura di Mondrian

Mondrian è strutturato con una architettura a 4 livelli:

  • Presentation layer: interfaccia di interazione tra utenti e sistema (es. Jpivot), attraverso cui eseguire interrogazioni multidimensionali (es. MDX)
  • Dimensional layer: parsing, validazione ed esecuzione query MDX
  • Star layer: gestione cache per dati aggregati
  • Storage layer: gestione delle sorgenti dati (RDBMS)

Per descrivere il modello multidimensionale e  definire i “cubi” si usa un file XML di metadati che specifica Fatti, Gerarchie, Misure e attributi.

Esempio: da Dimensional Fact Model a file di metadati Mondrian

Da DFM a Mondrian

 

See more at:
http://it.wikipedia.org/wiki/Mondrian_OLAP
http://www.appuntisoftware.it/pentaho-mondrian-un-motore-olap-per-java/
http://bias.csr.unibo.it/turricchia/Mondrian%20-%20Jpivot.pdf

Data Warehouse: Dimensional Fact Model (DFM)

Dimensional Fact Model (DFM): modello concettuale grafico di supporto alla progettazione di 
Data Warehouse. Può essere considerato come una specializzazione del modello multidimensionale per applicazioni di data warehousing (Golfarelli, 1998).

La rappresentazione concettuale generata dal DFM consiste in un insieme di schemi di fatto.

Gli elementi di base modellati dagli schemi di fatto sono i fatti, le misure, le dimensioni e le gerarchie:

  • Fatto. Un fatto è un concetto di interesse per il processo decisionale; tipicamente modella un insieme di eventi che accadono.
  • Misura. Una misura è una proprietà numerica di un fatto e ne descrive un aspetto quantitativo di interesse per l’analisi.
  • Dimensione. Una dimensione è una proprietà con dominio finito di un fatto e ne descrive una coordinata di analisi.
  • Gerarchia. Una gerarchia è un albero direzionato i cui nodi sono attributi dimensionali e i cui archi modellano associazioni molti-a-uno tra coppie di attributi dimensionali. Essa racchiude una dimensione, posta alla radice dell’albero, e tutti gli attributi dimensionali che la descrivono.

Schema di Fatto

Schema di Fatto con gerarchie di dimensioni

See more at:
http://caccio.blogdns.net/archives/101
http://www.fimietta.it/blog/9a-progettazione-concettuale-data-warehouse-il-dimensional-fact-model.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