Gestione indici FULL-TEXT Sql Server 2005

Scopo del presente articolo è quello di descrivere le caratteristiche degli indici FULL-TEXT di SQL SERVER 2005 riportando anche quali sono tutte le attività necessarie per la loro creazione e gestione essendo questi differenti rispetto a quelli tradizionali.

Gli indici full-text
Gli indici full-text sono quei particolari indici che servono per poter eseguire delle query “lingustiche”, attraverso l’uso di predicati proprietari del motore di database, sia su campi testuali che binari. Gli indici full-text si diversificano rispetto a quelli tradizionali per i seguenti aspetti:

• vengono archiviati nel file system, ma gestiti tramite database;
• è previsto un solo indice full-text per tabella;
• l’aggiunta dei dati agli indici, definita popolamento, può essere fatta tramite una pianificazione o una richiesta specifica, oppure avvenire in modo automatico a seguito dell’aggiunta di nuovi dati;
• l’indice viene creato all’interno di un catalogo full-text; La creazione, la gestione e l’eliminazione avviene tramite la “SQL Server  Management Studio” con procedure guidate o attraverso store procedure.

Queste differenze rendono necessarie diverse operazioni amministrative per la gestione di indici full-text che possono essere raggruppate nei seguenti gruppi di attività:

1. Gestione dei cataloghi;
2. Creazione degli indici; 
3. Popolamento degli indici;
4. Indicizzazione.

Di seguito vediamo nel dettaglio le singole attività.

I cataloghi
Innanzi tutti è necessario introdurre il concetto di catalogo che rappresenta il contenitore logico e fisico degli indici fulltext.
Un catalogo deve essere associato ad un database, possono esserci uno o più cataloghi per database, ma il singolo catalogo può essere associato ad un solo database.
Un catalogo può contenere più indici, in fase di creazione viene associato logicamente ad un filegroup, ma viene poi creato fisicamente su di un spazio disco definito a parte sul file system.
La possibilità di definire su quale filegroup e soprattutto su quale percorso fisico poter creare un catalogo permette di poter suddividere il carico hardware di I/O su diversi dischi.
La creazione e la gestione di un catalogo può avvenire attraverso l’uso delle istruzioni “CREATE FULLTEXT CATALOG”, “ALTER FULLTEXT CATALOG” e “DROP FULLTEXT CATALOG”.

Tutti i dettagli sulle modalità di popolamento degli indici li potete trovare all’indirizzo http://msdn2.microsoft.com/it-it/library/ms189520.aspx

La creazione di un indice
Prima di procedere con la creazione dell’indice e necessario creare, sulla tabella dove verrà creato l’indice fulltext, un indice UNICO formato da un unico campo che non accetti valori NULL e che permetta di identificare univocamente un record.
La richiesta di un campo univoco non NULL mette in evidenza come l’elemento di aggancio tra i dati che compongono l’indice full-text e i record presenti nella tabella indicizzata avvenga tramite la chiave univoca individuata e utilizzata nell’indice unico.

In questo contesto Microsoft consiglia, per avere migliori performance, di scegliere il campo che rappresenta la più piccola chiave indice disponibile, l’ideale è rappresentato da un campo di 4 byte,  e se possibile creare questo indice univoco sotto forma di indice CLUSTERED. 

Eseguiti i passi sopra indicati possiamo passare alla creazione dell’indice full-text che può avvenire attraverso il wizard presente nel “Sql Server Management Studio” oppure attraverso l’istruzione “CREATE FULLTEXT INDEX”. La creazione di un indice può essere fatta su uno o più campi di una stessa tabella, ma una tabella può contenere solo ed esclusivamente un indice. Un indice può essere creato sulle colonne di tipo char, varchar, nchar, nvarchar, text, ntext, image, xml e varbinary.

Tutti i dettagli sulle modalità di popolamento degli indici li potete trovare all’indirizzo http://msdn2.microsoft.com/it-it/library/ms187317.aspx

Il popolamento
La creazione di un indice full-text non comporta direttamente il suo popolamento come avviene per quelli classici, ma viene predisposto solo il “contenitore” dei dati indice. La fase di popolamento deve essere avviata in una seconda fase, con altri comandi e dopo aver definito la modalità di popolamento dell’indice stesso.
Infatti per un indice full-text è possibile definire diverse tipologie di popolamento:

• popolamento completo (full): consiste nel costruire un indice analizzando tutte le righe contenute in una tabella.
• popolamento incrementale (incremental): permette di aggionare un indice full-text aggiungendo solo i dati delle righe inserite, aggiornate o cancellate dalla tabella rispetto la precendente attività di popolamento. Questa funzionalità richiede la presenza di una colonna di tipo timestamp nella tabella oggetto dell’indicizzazione per poter determinare quali righe hanno subito una modifica.
• popolamento basato su rilevamento delle modifiche (Change Tracking): si basa sul tracciamento dei cambiamenti che subiscono le righe di una tabella. Tali cambiamenti vengono memorizzati in una tabella di sistema e Sql Server si occupa di propagare le modifiche nell’indice. La propagazione delle modifiche può essere configurata per avvenire in tre modalità:
  • Background;
  • On Demand;
  • Schedulato.

La scelta della tipologia di mantenimento di un indice basata su popolamento incrementale, piuttosto che quella basata sul change tracking deve essere fatta in base allo scenario in cui il database deve erogare il proprio servizio. La scelta di un popolamento incrementale è da preferire se volete un database sempre performante che non deve essere appesantito da attività aggiuntive in termini di inserimento e modifica dei dati e da procedure di indicizzazione eseguite in momenti non controllati. Di contro però dovete permettervi di poter aggiornare l’indice in momenti successivi all’inserimento dei dati nelle tabelle e la vostra struttura dati deve prevedere anche la presenza di un campo timestamp.
Viceversa la scelta di un popolamento di change tracking può tornare utile soprattutto se volete che l’aggiornamento dell’indice avvenga in maniera automatica rispetto alla modifica dei dati e se l’overhead richiesto da SQL Server non riduce le performace del sistema.

Per controllare la presenza di attività di popolamento in corso è possibile consultare il contenuto della tabella di sistema sys.dm_fts_index_population. In essa trovate una riga per ogni processo di popolamento in corso con l’indicazione di tutti i dati principali utili a capire su quali oggetti e in che modalità sta avvenendo un popolamento.

Tutti i dettagli sulle modalità di popolamento degli indici li potete trovare all’indirizzo http://msdn2.microsoft.com/it-it/library/ms142575.aspx

Il processo di indicizzazione
Quando viene iniziato un popolamento full-text, il motore di database inserisce grandi quantità di dati in memoria e indica al servizio MSFTESQL di iniziare l’indicizzazione. Il servizio MSFTESQL indicizza i dati di tipo carattere e i dati binari formattati contenuti in una o più colonne di una tabella. Tramite un componente gestore di protocollo, il motore full-text estrae dalla memoria i dati da sottoporre a ulteriore elaborazione al fine di creare un indice full-text.

Durante l’indicizzazione dei dati archiviati in una colonna varbinary(max) o image, vengono utilizzati degli appositi filtri che implementano l’interfaccia Ifilter per leggere i singoli documenti e con i quali viene estratto il testo in base al formato file specificato per tali dati, ad esempio Microsoft Word.  (L’indicizzazione di documenti merita un approfondimento dedicato.)
Nell’ambito dell’elaborazione, i dati di testo raccolti vengono sottoposti ad un word breaker per separare il testo in singoli token o parole chiave con regole diverse a seconda della lingua specificata.
Una volta individuate le singole parole chiave queste vengono nuovamente elaborate per eliminare le parole “noiose” e non significative con lo scopo di “normalizzare” i dati prima dell’archiviazione definitiva nell’indice. Terminate tutte le procedure di analisi dei dati si ha un situzione in cui sono stati creati tanti piccoli “frammenti” di indice che devono essere tutte riuniti in un unico indice master. Questa attività di raggruppamento rappresenta l’elaborazione definitiva per la creazione di un indice.

Word Breaker e Stemmer
Questo processo esegue l’analisi linguistica su tutti i dati con indicizzazione full-text. L’analisi linguistica riguarda la ricerca dell’inizio e della fine delle parole (isolamento delle parole) e la coniugazione dei verbi (flessione). In Microsoft SQL Server 2005 sono presenti word breaker per 23 impostazioni internazionali. Per un elenco delle lingue che supportano la ricerca full-text, vedere la tabella sys.fulltext_languages (Transact-SQL).

Parole non significative
Per assicurare l’efficienza dell’indice full-text, Microsoft SQL Server è dotato di un meccanismo che rimuove le parole più frequenti, inutili ai fini della ricerca.  Queste parole, definite non significative, sono elencate in file corrispondenti, specifici per le impostazioni internazionali utilizzate.
Nelle impostazioni internazionali per la lingua italiana, ad esempio, articoli, preposizioni e parole generiche quali “circa”, “oltre” etc. generici sono contenute nel file delle parole non significative italiane e vengono escluse dall’indice full-text poiché in pratica risultano inutili ai fini della ricerca.
I file delle parole non significative si trovano nella directory $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\.
La creazione di questa directory e l’installazione dei file delle parole non significative vengono eseguite durante l’installazione di SQL Server con il supporto della ricerca full-text. La cosa interessante è rappresentata dal fatto che è possibile modificare i files delle parole non significative aggiungendone di nuove in momenti successivi la prima installazione.

Conclusioni
In questo articolo ho cercato di fornire le informazioni utili per il lettore che avere una prima visione delle principali caratteristiche degli indici FULL-TEXT.
L’argomento è, dal mio punto di vista, sufficientemente ampio da richiedere più articoli per poter parlare di tutti i suoi aspetti. Personalmente sto lavorando sull’indicizzazione di documenti PDF, di cui in seguito vi farò avere dei dettagli.

Nel frattempo per avere tutti gli approfondimenti sui vari aspetti che caratterizzano la gestione degli indici potete consultare la guida MSDN a partire dall’indirizzo:
http://msdn2.microsoft.com/it-it/library/ms142545.aspx