23 giugno 2008

Analytics - n.1: NTILE

Con questo post inauguro la serie "Analytics", ovvero come utilizzare le potenti funzioni analitiche di Oracle 10g.

NTILE è una funzione che permette di ottenere il numero corrente di partizione dei dati, basandosi su un criterio di partizione determinato nella query.

Facciamo subito un esempio: ho una tabella che contiene i tempi di arrivo di una maratona con tanti partecipanti, diciamo 5000. Di questi, 1000 sono arrivati al traguardo, mentre gli altri si sono persi per strada.
La tabella contiene un ID univoco del corridore e il suo tempo di arrivo.

Supponiamo che io voglia dividere l'insieme degli "arrivati" in n gruppi omogenei, cioè aventi lo stesso numero di componenti.
Gli ID, anche se generati con una sequenza, sono distribuiti casualmente, dato che solo 1/5 dei corridori è arrivato alla fine; lo stesso vale per i tempi di arrivo.

La funzione NTILE ci permette di dividere il nostro set di dati in partizioni uguali, assegnando ad ogni riga il numero di partizione in cui si trova. Il criterio per trovarsi in una partizione o in un'altra è il tempo di arrivo in ordine crescente.

Dividiamoli ad esempio in 10 gruppi:
SQL> select * from marathon;

RUNNER_ID ARRIVAL_TIME
---------- -------------------
93 2008-06-19 13:11:39
3144 2008-06-19 12:44:10
4450 2008-06-19 12:54:54
453 2008-06-19 12:52:58
808 2008-06-19 12:48:00
4036 2008-06-19 13:11:39
4048 2008-06-19 13:06:48
660 2008-06-19 12:51:40
4091 2008-06-19 13:12:38
591 2008-06-19 13:02:55
[...]

Con la query seguente otteniamo la divisione in gruppi:
SQL> select runner_id, arrival_time, ntile(10) over (order by arrival_time) nt from marathon;

Come si può vedere, ci sono esattamente 100 corridori in ogni gruppo:
SQL> select nt, count(*) from
(select runner_id, arrival_time,
ntile(10) over (order by arrival_time) nt from marathon)
group by nt order by nt;

NT COUNT(*)
---------- ----------
1 100
2 100
3 100
4 100
5 100
6 100
7 100
8 100
9 100
10 100

10 rows selected.

Possiamo anche calcolare l'ntile su una partizione dei dati originari: immaginiamo per esempio che ai corridori siano state assegnate delle pettorine colorate di rosso se il loro id era tra 1 e 1000, verde se tra 1001 e 2000, blu se maggiore di 2000, divisi quindi per categoria.
Una query come la seguente divide in dieci sottogruppi ogni categoria, basandosi sempre sul tempo di arrivo:
SQL> select runner_id, arrival_time, color,
ntile(10) over (partition by color order by arrival_time) from marathon;

02 giugno 2008

ROWSCN, una soluzione ai lost update

Per le applicazioni web-based l'aggiornamento di una serie di record nel database presenta problemi diversi da quelli delle applicazioni tradizionali client-server, che di solito utilizzano il metodo "SELECT ... FOR UPDATE".

Facciamo un esempio: due utenti di una webapp caricano una form di attributi di una stessa persona (identificata solamente da una chiave primaria) e li modificano nello stesso intervallo di tempo. Trascurando l'opportunità della cosa, l'ultima che avrà cliccato su "aggiorna" scriverà i dati della form nel database, sovrascrivendo eventuali modifiche fatte dal primo utente.

Ci sono un paio di metodi per accorgersi questa situazione: fare la "SELECT FOR UPDATE" appena prima di modificare il record inserendo una condizione WHERE che contiene tutti i dati originali, oppure usare una colonna timestamp per sapere la data di ultima modifica.
Non ci interessa invece che cosa fa l'applicazione una volta accortasi della cosa: ciò dipende dalla business logic, ovvero dal tipo di applicazione.

In Oracle 10g è presente una nuova pseudo-colonna: ORA_ROWSCN. Essa contiene l'SCN relativo all'ultima operazione DML effettuata sul blocco che contiene la riga selezionata. In questo modo è possibile sapere se il blocco è stato modificato dall'ultima lettura effettuata; ma come si fa ad utilizzare a livello di riga? La dipendenza a livello di riga viene abilitata all'atto di creazione di una tabella con la clausola ROWDEPENDENCIES.

Facciamo un esempio:
SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 0 AAAdsYAAEAAAAD0AAA 1070257362
2 200 99 AAAdsYAAEAAAAD0AAB 1070257362
3 300 0 AAAdsYAAEAAAAD0AAC 1070257362
4 400 350 AAAdsYAAEAAAAD0AAD 1070257362
5 500 0 AAAdsYAAEAAAAD0AAE 1070257362
6 600 295 AAAdsYAAEAAAAD0AAF 1070257362
7 700 700 AAAdsYAAEAAAAD0AAG 1070257362
8 800 800 AAAdsYAAEAAAAD0AAH 1070257362

8 rows selected.

SQL> update claim set CLAIM_BAL = 1 where claim_num = 1;

1 row updated.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 1 AAAdsYAAEAAAAD0AAA 1070257362
2 200 99 AAAdsYAAEAAAAD0AAB 1070257362
3 300 0 AAAdsYAAEAAAAD0AAC 1070257362
4 400 350 AAAdsYAAEAAAAD0AAD 1070257362
5 500 0 AAAdsYAAEAAAAD0AAE 1070257362
6 600 295 AAAdsYAAEAAAAD0AAF 1070257362
7 700 700 AAAdsYAAEAAAAD0AAG 1070257362
8 800 800 AAAdsYAAEAAAAD0AAH 1070257362

8 rows selected.

SQL> commit;

Commit complete.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 1 AAAdsYAAEAAAAD0AAA 1201770241
2 200 99 AAAdsYAAEAAAAD0AAB 1201770241
3 300 0 AAAdsYAAEAAAAD0AAC 1201770241
4 400 350 AAAdsYAAEAAAAD0AAD 1201770241
5 500 0 AAAdsYAAEAAAAD0AAE 1201770241
6 600 295 AAAdsYAAEAAAAD0AAF 1201770241
7 700 700 AAAdsYAAEAAAAD0AAG 1201770241
8 800 800 AAAdsYAAEAAAAD0AAH 1201770241

8 rows selected.

SQL> create table claim_rowdep ROWDEPENDENCIES
as select * from claim;

Table created.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim_rowdep;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 1 AAAlQ9AAEAAAAIEAAA 1201772166
2 200 99 AAAlQ9AAEAAAAIEAAB 1201772166
3 300 0 AAAlQ9AAEAAAAIEAAC 1201772166
4 400 350 AAAlQ9AAEAAAAIEAAD 1201772166
5 500 0 AAAlQ9AAEAAAAIEAAE 1201772166
6 600 295 AAAlQ9AAEAAAAIEAAF 1201772166
7 700 700 AAAlQ9AAEAAAAIEAAG 1201772166
8 800 800 AAAlQ9AAEAAAAIEAAH 1201772166

8 rows selected.

SQL> update claim_rowdep set claim_bal = 0 where claim_num = 1;

1 row updated.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim_rowdep;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 0 AAAlQ9AAEAAAAIEAAA
2 200 99 AAAlQ9AAEAAAAIEAAB 1201772166
3 300 0 AAAlQ9AAEAAAAIEAAC 1201772166
4 400 350 AAAlQ9AAEAAAAIEAAD 1201772166
5 500 0 AAAlQ9AAEAAAAIEAAE 1201772166
6 600 295 AAAlQ9AAEAAAAIEAAF 1201772166
7 700 700 AAAlQ9AAEAAAAIEAAG 1201772166
8 800 800 AAAlQ9AAEAAAAIEAAH 1201772166

8 rows selected.

SQL> commit;

Commit complete.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim_rowdep;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 0 AAAlQ9AAEAAAAIEAAA 1201772868
2 200 99 AAAlQ9AAEAAAAIEAAB 1201772166
3 300 0 AAAlQ9AAEAAAAIEAAC 1201772166
4 400 350 AAAlQ9AAEAAAAIEAAD 1201772166
5 500 0 AAAlQ9AAEAAAAIEAAE 1201772166
6 600 295 AAAlQ9AAEAAAAIEAAF 1201772166
7 700 700 AAAlQ9AAEAAAAIEAAG 1201772166
8 800 800 AAAlQ9AAEAAAAIEAAH 1201772166

8 rows selected.


Come si vede dal ROWID i dati stanno tutti nello stesso blocco, mentre il ROWSCN cambia per tutto il blocco nella tabella claim e solo per la riga interessata dall'update nella tabella claim_rowdep.

Non è possibile aggiungere ROWDEPENDENCIES alla tabella già creata, bisogna farlo alla creazione: la clausola infatti comporta che ogni riga aumenti le proprie dimensioni di 6 bytes.