[SQL 2008] Panoramica FullText
Le funzionalità di indicizzazione nella nuova versione di SQL Server sono molto differenti rispetto alla precedente.
Differenti sia da un punto di vista di architettura, sia da un punto di vista di programmazione ed utilizzo.
Il motore fulltext, oggi, non è più in un processo separato, ma risiede direttamente all'interno dello stesso processo del motore del database (non esiste più il servizio MSFTESQL).
Allo stesso modo non è più necessario avere un filegroup dedicato (è tutto nel db – che si traduce anche a poter eseguire un backup / restore senza dover ricostruire gli indici).
Questa l’architettura in SQL Server 2008:
Il motore fornisce due attività ben precise:
- supporto all'indicizzazione
- supporto alle interrogazioni
Amminstrare queste caratteristiche si traduce in quattro diverse attività:
- creare indici e cataloghi fulltext
- modificare indici e cataloghi fulltext
- cancellare indici e cataloghi fulltext
- schedulare e mantenere il corretto popolamento (allineamento) degli indici
Nota: in SQL Server 2008, di default, tutti i database sono abilitati al fulltext
Per poter utilizzare le funzionalità è necessario definire le colonne delle tabelle che vogliamo siano accessibili tramite il motore di indicizzazione.
Ovvero:
- creare un catalogo fulltext che possa mantenere gli indici
- creare un indice fulltext sulla tabella (o vista indicizzata)
Proviamo a disegnare uno scenario, magari utilizzando il FILESTREAM di SQL Server 2008, e vediamo come poter creare ed utilizzare il motore di indicizzazione.
Abbiamo una tabella [documenti], modellata come segue:
CREATE TABLE [dbo].[documenti](
[idDocumento] [uniqueidentifier] ROWGUIDCOL NOT NULL PRIMARY KEY,
[nomeFile] [varchar](35) NULL,
[body] [varbinary](max) FILESTREAM NULL
) ON [PRIMARY] FILESTREAM_ON [FILESTREAMGROUP]
Vediamo i passi necessari per costruire un catalogo ed interrogarlo:
use myDatabase
go
/* creo il catalogo */
CREATE FULLTEXT CATALOG myDatabaseFTCat;
go
/* creo indice univoco */
CREATE UNIQUE INDEX ui_documenti ON dbo.documenti(idDocumento);
go
/* creo una stoplist */
CREATE FULLTEXT STOPLIST myDatabaseStopList;
GO
/* creo indice fulltext */
CREATE FULLTEXT INDEX ON dbo.documenti
(
body TYPE COLUMN nomeFile Language 1040
)
KEY INDEX ui_documenti ON myDatabaseFTCat
WITH CHANGE_TRACKING AUTO
GO
/* qualche esempio di ricerca */
DECLARE @SearchWord varchar(30)
SET @SearchWord ='"schemi" AND "relazionali"'
SELECT * FROM dbo.documenti
WHERE CONTAINS(body, @SearchWord);
go
DECLARE @SearchWord varchar(30)
SET @SearchWord ='relazionali er'
SELECT * FROM dbo.documenti
WHERE FREETEXT(body, @SearchWord);
DECLARE @SearchWord varchar(30)
SET @SearchWord = 'schemi'
SELECT nomeFile, T.rank FROM dbo.documenti D
join FREETEXTTABLE(dbo.Documenti, body, @SearchWord) as T
on D.idDocumento = T.[key]
order by T.rank desc
/* *** NOTE *** */
/* per vedere se il db ha il fulltext abilitato */
SELECT DATABASEPROPERTYEX('myDatabase', 'IsFullTextEnabled');
/* per verificare proprietà del catalogo */
SELECT FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'Populatestatus') as populateStatus,
FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'AccentSensitivity') as AccentSensitivity,
FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'IndexSize') as IndexSize,
FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'ItemCount') as ItemCount,
FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'LogSize') as LogSize,
FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'UniqueKeyCount') as UniqueKeyCount,
FULLTEXTCATALOGPROPERTY('myDatabaseFTCat', 'ImportStatus') as ImportStatus;
go
/* la tabella delle lingue */
select * from sys.fulltext_languages order by name
/* informazioni sul contenuto dell'indice */
SELECT * FROM sys.dm_fts_index_keywords(db_id('myDatabase'), object_id('dbo.documenti'))
/* Query per recuperare il numero di frammenti di ogni indice full-text
Se ne esistono diversi --> REORGANIZE per migliorare prestazioni */
select t.name as TableName
, f.data_size
, f.row_count
, case f.status
when 0 then 'Newly created and not yet used'
when 1 then 'Being used for insert'
when 4 then 'Closed ready for query'
when 6 then 'Being used for merge inpurt and ready for query'
when 8 then 'Marked for deletion. Will not be used for query and merge source'
else 'Unknown status code'
end
from sys.fulltext_index_fragments f
join sys.tables t on f.table_id = t.object_id;
Qualche query per mettere il naso dentro il motore...
SELECT *
FROM sys.dm_fts_index_keywords_by_document (db_id('myDatabase'), object_id('dbo.documenti'))
select * from
sys.dm_fts_parser ('"schemi relazionali"', 1040, (select stoplist_id from sys.fulltext_indexes),0)
select * from sys.dm_fts_parser ('"sia schemi che relazionali"', 1040,
(select stoplist_id from sys.fulltext_indexes),0)
Un esempio di piano di esecuzione:
Nota: di default SQL Server non ha la capacità di indicizzare file PDF.
E' però possibile installare il filtro che Adobe rilascia gratuitamente.
http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611