Índices FULL TEXT (SQL Server 2008)
Marta E. Zorrilla
M
ar
ta
¿Qué es la búsqueda full-text?
•
Búsqueda por texto libre (palabras, frases,..)
•
Hasta ahora para buscar palabras en un
campo ‘char’ / ‘varchar’ / ‘nvarchar’
LIKE
(búsqueda por patrones)
•
Pero… no permite la búsqueda por
• Dos o más palabras en cualquier orden,
búsquedas por frase, por proximidad o por
sinónimos.
• Ni proporciona resultados ordenados según el
peso que le damos a las palabras buscadas.
3 M ar ta Z o rr il la
Servicio Microsoft Search
• Es el motor de indexación y de búsqueda de texto de SQL Server.
• Realiza consultas de texto eficaces y sofisticadas sobre columnas que almacenan datos basados en caracteres o en ficheros (char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary).
• Diferencias existentes entre este servicio y las búsquedas con operador LIKE :
• En SQL server 2005 se almacenan en el sistema de archivos y no en la base de datos, aunque es la base de datos quien los administra. En 2008, se integran dentro del motor.
• Sólo se permite un índice de texto por cada tabla.
• El índice de texto se crea sobre una o más columnas de la tabla. Si se desea agregar datos a los índices de texto hay que realizar un llenado manual o programado (También se pueden llenar automáticamente en la inserción de datos).
M
ar
ta
Conceptos
• Catálogos de texto
• Para una base de datos de SQL Server 2008, un catálogo de texto
completo es un objeto virtual que no pertenece a ningún grupo de archivos; es un concepto lógico que hace referencia a un grupo de índices de texto completo.
• Índices de texto
• Un índice de texto completo almacena información sobre las palabras relevantes y su ubicación en una determinada columna.
• Los índices de texto se vinculan a una columna de una tabla. Esto es, se requiere que la tabla tenga un índice único lo más pequeño posible (no vale PK de dos o más columnas).
• Este índice que creamos sobre una columna de texto almacenará
información sobre cada palabra que contiene la columna y su ubicación dentro de la tabla.
• Los índices se pueden actualizar al modificar los datos de la tabla (seguimiento de cambios), o se pueden llenar a intervalos regulares (incremental).
• Estos procesos de llenado se suelen realizar de forma asíncrona y en segundo plano porque consumen tiempo y recursos.
5 M ar ta Z o rr il la
Conceptos (y 2)
• Separador de palabras• En un idioma concreto, un separador de palabras corta el texto en función de las reglas léxicas de dicho idioma.
• Testigo
• Palabra o cadena de caracteres identificada por el separador de palabras. • Lematizador
• En un idioma concreto, un lematizador genera formas con inflexión de una determinada palabra en función de las reglas de dicho idioma. Los
lematizadores son específicos del idioma. • Filtro
• En un tipo de archivo especificado, por ejemplo .doc, los filtros extraen el texto de un archivo almacenado en una columna varbinary(max) o image. • Llenado o rastreo
• Proceso de creación y mantenimiento de un índice de texto. • Palabras irrelevantes
• Palabras frecuentes que no ayudan en la búsqueda (preposiciones, artículos,…).
M
ar
ta
Proceso Information Retrieval
Text Words keys documents document + identifier interfaz keys Words to search
Relevant docs. Classification + ponderation
Words to index User Index engine Filtering Word selection Empty words Index generation Doc identification Document Statistics process Search Docs. Index generation
7 M ar ta Z o rr il la d p s w
d = código del documento p = número de párrafo s = número de frase w = palabra en la frase D = número de documentos Ficheros Indices A = número de ocurrencias
Lista de palabras ordenadas
Palabra Palabra D A Planta Planta Planta Planta 2 3 Diccionario
Índices invertidos
M
ar
ta
Estructura del índice
(SQL Server 2008)
9 Marta Zorrilla
M
ar
ta
Creación del catálogo
CREATE FULLTEXT CATALOG catalog_name [WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ] <catalog_option>::=
ACCENT_SENSITIVITY = {ON|OFF}
catalog_name: nombre del catálogo.
ACCENT_SENSITIVITY: Especifica si el catálogo distingue los acentos en la indización.
AS DEFAULT: especifica que el catálogo es el predeterminado
11 M ar ta Z o rr il la
Creación de índice FULL-TEXT
CREATE FULLTEXT INDEX ON table_name
[(column_name [TYPE COLUMN type_column_name] [LANGUAGE language_term] [,...n])]
KEY INDEX index_name
[ON fulltext_catalog_name]
[WITH {CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name } }]
table_name: tabla que contiene la columna o columnas incluidas en el índice de texto.
column_name: nombre de la columna/s incluidas en el índice de texto.
type_column_name: nombre de la columna de table_name que contiene el tipo de documento de column_name. Especifique type_column_name sólo si la columna/s de column_name son de tipo varbinary(max) o image
language_term: idioma de los datos almacenados en column_name.
index_name: nombre del índice único de table_name.
fulltext_catalog_name: catálogo de texto utilizado para el índice de texto.
WITH CHANGE_TRACKING: Especifica si SQL Server mantiene o no una lista de todos los cambios de los datos indizados.
M
ar
ta
Creación de STOPLIST
CREATE FULLTEXT STOPLIST stoplist_name
[ FROM { [ database_name. ] source_stoplist_name } | SYSTEM STOPLIST ] [ AUTHORIZATION owner_name ]
;
stoplist_name: Es el nombre de la lista de palabras irrelevantes
database_name: Es el nombre de la base de datos donde se encuentra la lista de palabras irrelevantes especificadapor source_stoplist_name. Si no se especifica, database_name utiliza de manera predeterminada la base de datos actual.
source_stoplist_name: Especifica que la nueva lista de palabras irrelevantes se crea copiando otra lista existente
SYSTEM STOPLIST: Especifica que la nueva lista de palabras irrelevantes se crea a partir de la lista predeterminada que hay en la base de datos Resource.
AUTHORIZATION owner_name: Especifica el nombre de una entidad de seguridad de base de datos como propietaria de la lista de palabras irrelevantes.
13 M ar ta Z o rr il la
Ejemplo
-- crear el catálogoCREATE FULLTEXT CATALOG [CatalogoDocumentos] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo] -- crear índice fulltext
CREATE FULLTEXT INDEX ON dbo.DOCS(descDoc LANGUAGE 'Spanish', ficheroDoc TYPE COLUMN tipoDoc LANGUAGE 'Spanish')
KEY INDEX PK_IDDOC ON CatalogoDocumentos WITH CHANGE_TRACKING OFF
M
ar
ta
Consultas al catálogo
•
CONTAINS
: Es un predicado que se utiliza para buscar en
columnas que contengan tipos de datos de tipo carácter
coincidencias exactas o aproximadas con palabras o frases.
•
CONTAINS puede buscar:
• Una palabra o una frase.
• El prefijo de una palabra o una frase. • Una palabra cerca de otra palabra.
• Una palabra que sea una inflexión de otra (por ejemplo, las
palabras controles, controladores, controlando y controlado son inflexiones de control).
• Una palabra que sea un sinónimo de otra palabra usando el diccionario de sinónimos (por ejemplo, la palabra metal puede tener sinónimos como aluminio y acero).
15 M ar ta Z o rr il la
Ejemplos
• Buscar todas las filas que contengan la palabra ‘queso’ SELECT * FROM docs WHERE
CONTAINS (descDoc,'queso')
• Buscar todas las filas que contengan ‘queso’ y ‘burgos’ SELECT * FROM docs WHERE
CONTAINS (descDoc,'queso AND burgos')
• Buscar todas las filas que tienen ‘queso’, ‘quesitos’, ‘quesada’… SELECT * FROM docs WHERE
CONTAINS(descDoc, '"ques*"')
• Buscar todas las filas que tienen la frase ‘queso de Burgos’ SELECT * FROM docs WHERE
M
ar
ta
Ejemplos
• Buscar todas las filas que tienen la palabra ‘queso’ próxima a ‘Burgos’ SELECT * FROM docs WHERE
CONTAINS(descDoc, ' queso NEAR Burgos ')
• Buscar todas las filas que tienen la palabra ‘queso’ o derivados SELECT * FROM docs WHERE
CONTAINS(descDoc, ' FORMSOF (INFLECTIONAL, queso) ')
• Buscar todas las filas que tienen la palabra ‘queso ’, ‘aceite’ asignando pesos
SELECT * FROM docs WHERE
CONTAINS(descDoc, 'ISABOUT (queso WEIGHT (.8),aceite WEIGHT (.2) )'); • Buscar en la descripción o en el fichero las palabras vino o pasterizada SELECT * FROM docs
17 M ar ta Z o rr il la
Consultas al catálogo (y 2)
•
Predicado
FREETEXT
: Se utiliza para buscar en columnas de
tipo carácter valores que coincidan con el significado de la
condición de búsqueda y no estrictamente con las palabras.
Cuando se utiliza FREETEXT el motor separa la cadena
buscada internamente en palabras que son términos de
búsqueda y se asigna a cada uno de los términos un peso y se
buscan las coincidencias.
•
P.ej. queremos saber sobre la fabricación de productos
lácteos.
SELECT * FROM Docs
WHERE FREETEXT (DescDoc, 'quiero información sobre la fabricación del queso')
M
ar
ta
Consultas al catálogo (y 3)
•
Función
CONTAINSTABLE
• Devuelve una tabla con una o más filas para aquellas
columnas que contengan datos de tipo carácter en las que
haya coincidencias exactas o aproximadas (menos precisas)
con palabras simples o frases, palabras próximas a otra
dada (dentro de una cierta distancia) o bien coincidencias
ponderadas.
• CONTAINSTABLE se puede utilizar en una cláusula FROM
de una instrucción SELECT como si fuera un nombre de
tabla.
• Las consultas que utilizan CONTAINSTABLE especifican
consultas de texto de tipo CONTAINS que devuelven un
valor de clasificación por porcentaje de aciertos (RANK) y un
valor de clave de texto (KEY) por cada fila.
• La función CONTAINSTABLE utiliza las mismas condiciones
de búsqueda que el predicado CONTAINS.
19 M ar ta Z o rr il la
Ejemplo
•
Devuelve el id del documento, su descripción y relevancia de
los documentos que contienen en su descripción palabras que
empiecen por ‘aceit’ o que contengan la palabra vino
SELECT docs.iddoc,docs.descDoc, KEY_TBL.RANK
FROM docs INNER JOIN
CONTAINSTABLE(docs, descdoc, '"aceit*" or vino')
AS KEY_TBL
ON docs.iddoc= KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK
M
ar
ta
Consultas al catálogo (y 4)
•
Función
FREETEXTTABLE
• Devuelve una tabla de cero, una o más filas para las
columnas que contienen datos de tipo carácter cuyos
valores coinciden con el significado, aunque no literalmente,
del texto especificado en la cadena de consulta.
• Se puede hacer referencia a FREETEXTTABLE en la
cláusula FROM de las instrucciones SELECT, como un
nombre de tabla normal.
• Las consultas que utilizan FREETEXTTABLE son consultas
de texto libre que devuelven un valor de clasificación por
importancia (RANK) y una clave de texto (KEY) para cada
fila.
21 M ar ta Z o rr il la
Ejemplo
•
Devuelve el id del documento, su descripción y
relevancia de los documentos que contienen en su
descripción palabras o términos de la frase que se
indica
SELECT docs.iddoc,docs.descDoc, KEY_TBL.RANK
FROM docs INNER JOIN
FREETEXTTABLE(docs, descdoc, 'quiero información sobre la
fabricación de productos lácteos') AS KEY_TBL
ON docs.iddoc= KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK
M
ar
ta
Actualización del índice y catálogo
• Para iniciar el seguimiento de cambios, debe especificarse la opción WITH CHANGE_TRACKING en la instrucción CREATE FULLTEXT INDEX. Cuando se utiliza el seguimiento de cambios, puede
especificarse el modo en que se propagarán los cambios al índice de texto (manual, auto, off). También se puede realizar a través de
procedimientos almacenados
• activar y propaga cambios mientras se producen EXEC sp_fulltext_table 'Docs', 'Start_change_tracking';
EXEC sp_fulltext_table 'Docs', 'Start_background_updateindex';
• El catálogo se deberá reconstruir (build) cuando se cambia alguna característica relevante del mismo como ‘accent sensitivity ‘
• El llenado incremental del catálogo actualiza el índice de texto de las filas que se hayan agregado, eliminado o modificado desde el último llenado o en el curso del último llenado. Para realizar un llenado
incremental, es necesario que la tabla indizada tenga una columna del tipo de datos timestamp, sino hace un llenado completo.
23 M ar ta Z o rr il la
Procedimientos almacenados
relacionados
(hay muchos más)
• sp_fulltext_catalog: Crea y quita un catálogo de texto, e inicia y detiene la acción de indización de un catálogo.
• sp_fulltext_column: Especifica si una columna concreta de una tabla participa en la indización de texto
• sp_fulltext_keymappings: Devuelve las asignaciones entre los
identificadores de documento (DocIds) y los valores de clave de texto completo.
• sp_fulltext_service: Cambia las propiedades del servicio de búsqueda de texto para SQL (MSFTESQL) de Microsoft.
• sp_fulltext_table: Marca o quita la marca de una tabla para la
indización de texto (desaparece en siguiente versión, usar CREATE, ALTER, DROP INDEX FULLTEXT).
• sp_help_fulltext_columns • sp_help_fulltext_tables
• sp_help_fulltext_catalogs_cursor: sp_help_fulltext_columns_cursor • sp_help_fulltext_tables_cursor
M
ar
ta
Backup y restore
•
Se hace con el de la BD, y recarga
25 M ar ta Z o rr il la
