Oracle SQL clausola LIKE e accesso agli INDICI

O

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.

A proposito di me

Dario Frongillo

Fondatore di Italiancoders e dell iniziativa devtalks.
Software architect con un forte background in Java, Architettura REST, framework Spring , Database Design,progettazione e sviluppo di SPA e RIA Web application con framework Javascript. Attualmente mi occupo di sviluppo soluzioni software in ambito Banking e Finance: in particolare progettazione e sviluppo di applicativi web based per la realizzazione di sistemi di trading, interfacce con i mercati finanziari e di servizi real time.

I nostri Partner

Gli articoli più letti

Articoli recenti

Commenti recenti