Oracle mette a isposizione la potentissima clausola WITH che permette di assegnare un nome a una subquery e di poterla referenziare in altre subquery o nella query principale con l’alias definito.

In altri termini la clausola WITH permette di creare delle tabelle temporanee frutto dell’esecuzione di una query e di poter usare queste tabelle temporanee, referenziate con un nome, nella query di ricerca.

La sintassi della clausola WITH è la seguente:

WITH
  alias1 AS 
  (subquery1),
  alias2 AS
  (subquery2),
     .
     .
     .  
  aliasN AS 
  (subqueryN)
SELECT lista_colonne
FROM lista_tabelle
WHERE lista_condizioni;

alias: sono i nomi con i queli vengono identificate le subquery e con i quali verranno referenziate nelle altre subquery e in quella principale

subquery: sono gli statement SQL di selezione che potranno essere refrenziati successivamente

Gli alias delle subquery potranno comparire nella lista tabelle e i loro campi nella lista_condizioni.

Una clausola di WITH è molto utile quando i risultati delle query di WITH sono richiesti più di una volta nel corpo della query principale come ad esempio nei valori delle clausole di where.

Cos’è una cache

Una cahce è una memoria temporanea nella quale viene memorizzato un sottoinsieme di dati appartenenti ad un’altra memoria più lenta per velocizzarne il recupero.

Quando un dato deve essere recuperato viene prima fatto un tentativo per vedere se esso si trova nella cache, in caso contrario viene scandita anche la memoria più lenta. Con questa tecnica si punta a velocizzare il recupero dei dati.

Solitamente il sottoinsieme dei dati contenuti nella cahce non è casuale ma rispetta uno o più di questi criteri:

• i dati più utilizzati
• i dati utilizzati più di recente
• i dati vicini logicamente o fisicamente a i più utilizzati
• i dati vicini logicamente o fisicamente a i più recenti
• altri criteri arbitrari a seconda dei casi d’uso

Come è fatta

Si può immaginare un sistema di cache come una struttura piramidale in cui al dalla base alla punta si hanno le seguenti caratteristiche:

• aumento della velocità o per meglio dire di tempo di accesso (MB/s)
• diminuzione della capacità (MB)
• aumento del costo per unità di spazio (€/MB)

Si evince quindi che verso la punta staranno solo i dati più “importanti” mentre nella base staranno tutti i dati.

Problematica

Può verificarsi che i dati sulla memoria principale (la più lenta), non siano congruenti con quelli contenuti nella cache, questo può succedere perche alcune cache non supportano la modifica dei dati ma sono usate in sola lettura. Per ovviare a questo problema si utilizzano dei meccanismi di scadenza dei dati memorizzati al passare di un tot di tempo o al verificarsi di determinati eventi.

Esempi

Un tipico esempio di un sistema di cache è quello dell’architettura di un processore che reperisce i dati dalla memoria di massa (Hard disk) attraverso diversi livelli di cache interna e attraverso la RAM. L’hard disk rappresenta la memoria più lenta mentre la cache di primo livello delprocessore quella più veloce, non è difficile quindi immaginarsi queste memorie nel sistema piramidale sopra descritto.

Quando parliamo di un sistema di cache non per forza ci dobbiamo riferire a memorie intese come componenti hardware (anche se alla fine tutto e riconducibile all’hardware), immaginiamo ad esempio la cache del browser che  memorizza le pagine web in modo tale che se facciamo richiesta di una pagina già vista questa è reperita dalla cache (se non scaduta) e non ricaricata dal web. In questo caso base della piramide è il server web il cui tempo di accesso è quello determinato dalla connessione di rete (e non solo).

Altri esempi di cache che non si riferisce direttamente all’hardware sono ad esempio i DNS e le cahce dei motori di ricerca.

Oracle mette a disposizione innumerevoli funzioni analitiche che agevolano la scrittura si query particolarmente complesse e spesso migliorandone le performance.

In questo articolo saranno descritte, con un breve caso d’uso, le funzioni che permettono di elaborare matematicamente dati numerici:

SUM(campo_1) OVER (PARTITION BY campo_2)

Questa funzione calcola la somma cumulativa dei valori del campo_1 raggruppando i record da sommare per il campo_2. la parola chiave partition serve per partizionare il result set in insiemi sui quali viene poi applicata la funzione analitica di somma.

SUM(campo_1) OVER (ORDER BY campo_2, campo_3)

Questa funzione serve per calcolare i totali parziali del campo_1. Dopo che i dati sono stati ordinati per campo_2, campo_3 viene sommado il dato in esame con il precedente.

SUM(campo_1) OVER ()

Calcola il totale assoluto. Questa funzione è equivalente a SUM(campo_1) che restituisce un solo valore, a differenza di questa la funzione analitica associa il valore ritornato ad ogni record risultante.

SUM(campo_1) OVER (PARTITION BY campo_2 ORDER BY campo_3, campo_4)

Esegue un mix delle prime due, ovvero calcola un totale parziale del campo_1 raggruppando per il campo_2 e ordinando per i campi campo_3 e campo_4.

Vediamo un esempio:

SELECt ufficio,
  nome,
  salario,
  SUM(salario) OVER (PARTITION BY ufficio
    ORDER BY salario,nome) CUM_SAL_UFFI,
  SUM(salario) OVER (PARTITION BY ufficio) TOT_UFFI,
  SUM(salario) OVER (ORDER BY ufficio,salario) CUM,
  SUM(salario) OVER () TOT
FROM impiegati
ORDER BY ufficio, salario;

UFFICIO     NOME  SALARIO  CUM_SAL_UFFI   TOT_UFFI        CUM        TOT
------- --------  -------  ------------ ---------- ---------- ----------
10      BIANCHI      1300          1300       8750       1300      29025
10      FERRARI      2450          3750       8750       3750      29025
10      ROSSI        5000          8750       8750       8750      29025
20      BANFI         800           800      10875       9550      29025
20      DE AMICIS    1100          1900      10875      10650      29025
20      DEL PIERO    2975          4875      10875      13625      29025
20      SACCHI       3000          7875      10875      19625      29025
20      VERDI        3000         10875      10875      19625      29025
30      ANASTASI      950           950       9400      20575      29025
30      GABBANA      1250          2200       9400      23075      29025
30      GUCCI        1250          3450       9400      23075      29025
30      MIRRI        1500          4950       9400      24575      29025
30      PISANO       1600          6550       9400      26175      29025
30      RICCI        2850          9400       9400      29025      29025

14 righe selezionate.

Dalla tabella degli impiegati andiamo a selezionare:

• UFFICIO, NOME, SALARIO
• CUM_SAL_UFFI: il salario cumulativo di ogni ufficio
• TOT_UFFI: il salario totale di ogni ufficio
• CUM: i salari cumulativi degli impiegati
• TOT: il salario totale di tutti gli impiegati