picoSQL - Manuale di riferimento

8) Ottimizzatore delle query

picoSQL si ricava la possibilità di usare un indice analizzando la condizione di ricerca ed è per questo motivo che è importante, specialmente su tabelle con molte righe, la definizione degli indici e l'utilizzo di condizioni di ricerca che li sfruttino.

Per illustrare in modo semplice come deve essere strutturata una condizione, supponiamo intanto di avere un indice di una sola colonna di nome I.

L'ottimizzatore di picoSQL ricava la propria strategia di ricerca analizzando l'interrogazione in modo formale, non considerando cioè i valori effettivi con cui le colonne vengono confrontate né, di conseguenza, i valori realmente presenti nella base dati. Per capire questo punto, bisogna considerare che spesso vengono fatte delle query usando i parametri, come nel caso seguente:

I = ?

Questa query può essere eseguita più volte, sostituendo ogni volta un valore diverso al posto del punto interrogativo. È evidente che in casi come questo il modo più efficiente di procedere è di eseguire un'unica analisi del comando e stabilire una strategia di ricerca valida per qualsiasi parametro.

Fare l'analisi conoscendo il valore del parametro costringerebbe a ripetere l'analisi ogni volta che quest'ultimo viene modificato, non apportando, nella quasi totalità dei casi, informazioni utili a migliorare la strategia di ricerca.

Su questi presupposti, picoSQL riesce sempre a individuare un indice di ricerca, purché ne esista uno usabile.

Affinché un indice possa essere usato per una ricerca, una condizione deve individuare sulla colonna dell'indice uno o più intervalli esaustivi di valori che contengano tutte le righe richieste, come, per esempio, la seguente:

I < 5

Per il fatto che l'ottimizzazione non tiene conto dei dati può accadere che l'utilizzo di un indice non porti alcun vantaggio: supponendo, per esempio, che nel database siano presenti solo righe in cui la colonna I ha sempre un valore inferiore a 5, la condizione di ricerca precedente non limita in alcun modo la lettura della tabella e sarebbe stata più efficiente una scansione sequenziale.

Condizioni in AND a una condizione che individua un intervallo esaustivo non influenzano la valenza di un indice, per cui anche la seguente condizione permette l'utilizzo dell'indice:

I < 5 AND A > 10

Le cose cambiano se, al posto di una AND si usa una OR. Nella condizione seguente:

I < 5 OR A > 10

non è detto che le righe che ci interessano rientrino nell'intervallo definito dall'indice (I < 5) per cui tale intervallo non è più esaustivo e l'uso dell'indice diventa inutile. La cosa non migliora neppure se ci fosse un'indice definito sulla colonna A, in quanto neanche questo intervallo sarebbe esaustivo. L'analisi di entrambi gli intervalli, con eliminazione di eventuali sovrapposizioni, sarebbe pesante da un punto di vista di calcolo e potrebbe portare anche a una doppia scansione completa della tabella. In questi casi quindi viene eseguita una scansione sequenziale.

Quest'ultima osservazione ci induce a una prima semplice regola empirica: nelle ricerche è meglio non usare l'operatore logico OR perché porta a scandire l'intera tabella. Questo però non è sempre vero: ci sono infatti casi in cui anche in presenza di OR si riesce a individuare degli intervalli esaustivi, come, per esempio:

I < 5 OR I > 10

In questo caso gli intervalli sono due, ma racchiudono tutte le righe richieste. picoSQL è in grado di sfruttare gli indici in presenza di più intervalli esaustivi, anche nel caso che tali intervalli si sovrappongono, come nel caso:

I > 5 OR I > 10

In presenza di indici composti da più colonne, la teoria non cambia, anche se può essere più complicato capire se la ricerca impostata può essere fatta usando un indice o meno.

Per prima cosa l'elemento in posizione n di un indice composto può essere usato in una ricerca se è n = 1 oppure se può essere usato l'elemento in posizione n - 1. Supponiamo di avere un indice composto formato dalle colonne I, J e K e di avere le seguenti condizioni di ricerca:

I > 5 AND J < 10 AND K = 4

I > 5 AND K = 4

K = 4

Nella prima ricerca è possibile usare l'indice in tutte le sue parti, nella seconda si può usare solo la prima parte dell'indice mentre nella terza l'indice non può essere usato.

Fatta questa considerazione, le regole di utilizzo di un indice sono le stesse già viste per indici semplici. Analizzando le seguenti condizioni:

(I = 5 AND J = 10 AND K = 4) OR (I = 6 AND J = 11 AND K = 5)

(I = 5 AND J = 10 AND K > 4) OR (I = 5 AND J > 10) OR I > 5

si può notare che la prima usa completamente tutti e tre gli indici mentre la seconda utilizza solo la componente sulla colonna I; in tal caso infatti J e K possono avere un valore qualsiasi e quindi non possono essere usati come indice. In realtà, analizzando i valori attentamente si nota che sarebbe possibile individuare un intervallo esaustivo più stretto, ma questo comporterebbe un analisi contestuale che picoSQL non fa.