Quando si analizza il piano di esecuzione di una query Oracle, una delle prime cose che tende a far scattare un campanello d’allarme è questa:

TABLE ACCESS FULL

La reazione istintiva è spesso:

«Sta facendo un Full Table Scan. Manca un indice.»

Ma non è necessariamente così. Un Full Table Scan può essere una scelta perfettamente razionale dell’optimizer e, in alcuni casi, può essere più efficiente dell’utilizzo di un indice.

Vediamolo con un caso reale.

Cos’è un Full Table Scan?

Un Full Table Scan è una modalità di accesso ai dati in cui il DBMS (Oracle in questo caso) legge l’intera tabella, blocco dopo blocco, invece di utilizzare un indice per individuare preventivamente le righe che interessano.

Supponiamo di avere una tabella DOM contenente 500.000 righe e di eseguire:

SELECT *
FROM DOM
WHERE id_organizzazione = 9;

Oracle ha, semplificando, due possibili strategie.

Se utilizza un indice su ID_ORGANIZZAZIONE, cerca nell’indice il valore 9, ricava i ROWID delle righe corrispondenti e usa questi ultimi per recuperare i dati dalla tabella.

Se sceglie invece un Full Table Scan, legge direttamente tutti i blocchi della tabella e, durante la scansione, conserva soltanto le righe per cui:

id_organizzazione = 9

Nel piano di esecuzione questa seconda scelta appare tipicamente come:

TABLE ACCESS FULL  DOM

A prima vista leggere l’intera tabella può sembrare necessariamente inefficiente. Ma dipende da quanta parte della tabella deve essere recuperata.

Se su 500.000 righe ne servono soltanto 10, leggere tutta la tabella sarebbe evidentemente uno spreco e un indice potrebbe essere enormemente più efficiente.

Se invece ne servono 200.000, la situazione cambia: effettuare centinaia di migliaia di accessi alla tabella tramite i ROWID trovati nell’indice può costare più di una lettura sequenziale dell’intera tab

La query

Consideriamo quattro tabelle, che chiameremo:

• DOM: circa 490.000 righe;
• DOMSED: circa 600.000 righe;
• SED: circa 27.000 righe;
• CINV: 17 righe.

La query originale può essere semplificata nella forma seguente:

SELECT
    EXTRACT(YEAR FROM s.data_seduta) AS anno,
    COUNT(*) AS n,
    inv.descr AS classe_invalidita,
    d.id_organizzazione
FROM DOM d
JOIN DOMSED ds
    ON ds.id_domanda = d.id
JOIN SED s
    ON s.id = ds.id_seduta
JOIN CINV inv
    ON inv.id = ds.id_classi_inv
WHERE d.id_organizzazione = 9
  AND ds.id_classi_inv IN (6, 10)
GROUP BY
    d.id_organizzazione,
    EXTRACT(YEAR FROM s.data_seduta),
    ds.id_classi_inv,
    inv.descr
ORDER BY
    d.id_organizzazione,
    anno DESC,
    ds.id_classi_inv DESC;

Il piano di esecuzione mostra, tra le altre cose, due operazioni apparentemente sospette:

TABLE ACCESS FULL  DOM
TABLE ACCESS FULL  DOMSED

Le due tabelle hanno rispettivamente circa 490.000 e 600.000 righe. Verrebbe quindi spontaneo chiedersi se non sia possibile velocizzare la query utilizzando degli indici.

Gli indici, però, ci sono già.

In particolare esistono indici su:

DOM.ID_ORGANIZZAZIONE
DOMSED.ID_CLASSI_INV

cioè esattamente sulle colonne utilizzate nei due filtri:

WHERE d.id_organizzazione = 9
  AND ds.id_classi_inv IN (6, 10)

Perché Oracle decide di ignorarli?

La selettività dell’indice

Per capirlo possiamo misurare quante righe vengono effettivamente selezionate dalla prima condizione:

SELECT
    COUNT(*) totale,
    COUNT(CASE
        WHEN id_organizzazione = 9 THEN 1
    END) organizzazione_9
FROM DOM;

Otteniamo:

TOTALE    ORGANIZZAZIONE_9
489723    165353

Quindi la condizione seleziona circa:

[
\frac{165353}{489723}\cdot100 \approx 33,76%
]

della tabella.

Non è affatto poco: stiamo chiedendo a Oracle circa una riga su tre.

La situazione di DOMSED è simile:

righe totali:               600298
righe con ID_CLASSI_INV
uguale a 6 oppure 10:        100536

ossia:

[
\frac{100536}{600298}\cdot100 \approx 16,75%
]

Anche qui non stiamo cercando poche righe isolate: vogliamo oltre 100.000 righe.

Un indice non è gratis

Questo è il punto fondamentale.

Avere un indice non significa che utilizzarlo sia automaticamente conveniente.

Semplificando, utilizzando un indice Oracle deve:

1. percorrere l’indice per trovare i valori desiderati;
2. ottenere i ROWID delle righe corrispondenti;
3. utilizzare quei ROWID per andare a recuperare dalla tabella i dati necessari.

Se la query deve recuperare poche righe, questo meccanismo è estremamente efficiente.

Se invece dobbiamo recuperare 165.000 righe su 490.000, gli accessi alla tabella attraverso i ROWID possono diventare molto costosi.

A quel punto può essere più conveniente dire, in sostanza:

«Leggo direttamente tutta la tabella una volta e, mentre la leggo, scarto le righe che non mi interessano.»

Ed è precisamente ciò che consente un Full Table Scan.

Oracle lo sa

C’è un altro particolare interessante nel piano di esecuzione.

Per DOM, Oracle stimava che dopo il filtro sarebbero rimaste circa 169.000 righe. Il valore reale era circa 165.000.

Per DOMSED, Oracle stimava circa 101.000 righe. Il valore reale era circa 100.500.

Le statistiche consentivano quindi all’optimizer di avere un’idea molto buona della quantità di dati coinvolta.

Oracle non stava facendo un Full Table Scan perché non aveva trovato l’indice.

Sapeva che l’indice esisteva e ha deciso che non conveniva utilizzarlo.

E gli HASH JOIN?

Il piano utilizzava anche degli HASH JOIN.

Anche questa scelta è coerente con la quantità di dati coinvolta.

Dopo aver filtrato le due tabelle principali abbiamo infatti, approssimativamente:

DOM                         DOMSED
489.000                     600.000
   |                           |
   | filtro                    | filtro
   v                           v
165.000                     100.000
       \                     /
        \                   /
             HASH JOIN
                 |
                 v
             ~52.000

Non stiamo quindi lavorando su una manciata di record. Dobbiamo combinare insiemi costituiti da decine o centinaia di migliaia di righe.

Il ricorso a scansioni sequenziali e HASH JOIN è quindi perfettamente plausibile.

Ma la query è lenta?

Ed ecco forse il dato più importante di tutti.

No.

Sul database reale la query viene eseguita in meno di un secondo.

Stavamo quindi cercando di capire se fosse possibile eliminare dei Full Table Scan da una query che, nonostante legga tabelle da circa mezzo milione di righe, restituisce il risultato praticamente immediatamente.

Questo è un buon momento per fermarsi.

La lezione

Un piano di esecuzione non va letto cercando semplicemente le parole TABLE ACCESS FULL per poi eliminarle.

La domanda corretta non è:

«Oracle sta usando un indice?»

ma:

«Qual è il modo meno costoso per recuperare la quantità di dati richiesta da questa query?»

Se cerchiamo 10 righe in una tabella da un milione di record, un indice può fare una differenza enorme.

Se ne dobbiamo leggere 300.000, attraversare un indice per poi saltare continuamente dall’indice alla tabella può essere meno conveniente di una scansione completa.

Per questo:

Full Table Scan non significa query non ottimizzata.

E, simmetricamente:

la presenza di un indice non significa che Oracle debba usarlo.

L’optimizer si chiama così proprio perché il suo compito non è utilizzare il maggior numero possibile di indici, ma scegliere il piano che stima essere meno costoso.

E qualche volta la scelta migliore è semplicemente leggere tutta la tabella.

Riferimenti

• DBADEEDS
• With a little help from my friend ChatGPT