iNTRODUZIONE
L’errore più comune fatto da uno sviluppatore SQL è quello di utilizzare il like con il wildcard % per ricerche all’interno di un campo stringa, senza tenere conto che sta degradando le performance della query. Ad esempio ricercare tutti i prodotti che hanno una descrizione contenente la parola chiave “cibo”:
SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%CIBO%'
La query sopra riportata in presenza di molti record potrebbe degradare le performance, in quanto per Oracle la ricerca utilizzando il filtro like con wildcard %stringa% è molto onerosa.
A questo punto l’errore piu comune è quello di pensare che creare l’indice sul campo description risolva il problema:
CREATE INDEX PRODUCT_DESCRIPTION_IDX ON PRODUCTS (DESCRIPTION)
Assolutamente non vero. Ve lo dimostro tramite un explain plan dei piani di accesso di questa query:
EXPLAIN PLAN FOR select * from PRODUCTS WHERE DESCRIPTION LIKE '%CIBO%';
Plan hash value: 4056451428 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| PRODUCTS | -----------------------------------------------
Come potete notare l’indice PRODUCT_DESCRIPTION_IDX appena creato non viene utilizzato dalla query, causando un table access full pericolosissimo per tabelle molto grandi.
Cosa è accaduto? Oracle non utilizza l’indice per query che fanno uso di filtri con il wildcard %.
NB: Usando parzialmente il wildcard %, come ad esempio LIKE ‘CIBO%’, l’indice viene utilizzato correttamente. Purtroppo in determinati contesti un filtro per trovare tutte le stringhe che iniziano con una determinata stringa non è sufficiente.
possibile soluzione: index hint
È possibile forzare Oracle ad utilizzare uno o più indici attraverso la funzionalità di index hint, la quale consiste nell’inserire la seguente stringa dopo la clausola select:
/*+index(TABLENAME, INDEX)*/
Applicando questa tecnica alla nostra precedente query:
EXPLAIN PLAN FOR select /*+index(products, PRODUCT_DESCRIPTION_IDX)*/* from products where e.description like '%cibo%'
Oracle utilizzerà l’indice PRODUCT_DESCRIPTION_IDX per ritornare tutti i prodotti che hanno una descrizione contenente la stringa cibo.
Conclusione
Fate molta attenzione ad utilizzare like con il wildcard %; se proprio non potete farne a meno la soluzione che fa uso di index hint permette di ovviare questa problema, forzando oracle ad utilizzare l’indice di ricerca per migliorare le performance ed evitare un full scan sulla vostra tabella.
