Un índice con filtro -también llamado condicional- es un tipo especial de índice que se forma no con todos los registros de la tabla, si no que se forma con solo un subconjunto de las fila. En SQL Server la forma de crear este tipo de filtro es añadiendo la cláusula WHERE durante el CREATE INDEX, de tal manera que primero se filtran los registros que indica el WHERE y con ese subconjunto se forma la estructura del índice condicional.
La ventaja de este tipo de índice es que la estructura de un índice que abarca solo un subconjunto de los registros es que son más pequeños que su equivalente sin el filtro, de tal manera que si se utilizan pueden resultar en tiempos de respuesta más cortos.
Ejemplo
Suponiendo que existen dos tablas con el mismo contenido (mismos campos, mismos registros) y que a uno de ellos le hacemos un índice tradicional por los campos entidad, mun, y ageb, y en la segunda tabla creamos un índice por los mismos campos pero con un filtro por una sola entidad, ¿qué pasaría si ejecutamos el mismo query buscando registros dentro de las tablas para ver la diferencia?
Construcción de los índices.
Vamos a crear primero los índices sobre las tablas
-- Índice tradicional, sin filtro, sobre tres campos de la tabla DATOS CREATE INDEX indiceTODO ON datos (entidad, mun, ageb); -- Índice con filtro, sobre tres campos de la tabla DATOS_CON_INDICE de una sola entidad CREATE INDEX indiceWHERE ON datos_con_indice (entidad, mun,ageb) WHERE entidad='10';
Estas instrucciones crean dos índices -uno en cada tabla que tienen la misma estructura y mismo número de registros- pero uno de ellos es un índice condicional. El índice con filtro se hace sobre una sola entidad (la 10) por lo que únicamente será útil cuando se hagan consultas sobre la entidad 10.
Ejecución del mismo query y revisión de planes de ejecución
SELECT entidad, mun, ageb, pobtot FROM datos WHERE entidad ='10' AND mun='001' AND ageb='0054';
SELECT entidad, mun, ageb, pobtot FROM datos_con_indice WHERE entidad ='10' AND mun='001'AND ageb='0054';
Si ejecutamos estos querys (que obtendrán el mismo número de registros porque las tablas son iguales) sobre la entidad 10 podremos ver los planes de ejecución y la diferencia de costos.
En este caso la diferencia es de 88 a 12% (sin y con filtro condicional respectivamente).
Tamaño de los índices con filtro
Visto pues que un query que utiliza un índice con filtro es más eficiente, generalmente, que aquel que utiliza un índice equivalente pero sin filtro -sobre todos los registros- podemos ahora comparar el tamaño de los índices y verificar las páginas que ocupan. Para ello ejecutaremos el siguiente query que obtiene el tamaño de todos los índices de una base de datos.
--Obtención del tamaño de los índices de todas las tablas en la base de datos activa SELECT i.name [Índice], --Nombre de índice SUM(s.[used_page_count]) * 8 AS [Tamaño en KBytes] --Tamaño del índice FROM sys.dm_db_partition_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id] WHERE i.name LIKE 'indice%' --Índices que comienzan con "indice" GROUP BY i.[name] ORDER BY 1
El resultado es el siguiente
En este caso, el índice con filtro o condicional ocupa 1,256 KB, pero el filtro tradicional -que aplica sobre todos los registros- ocupa 21,576 KBytes.
Cuándo es útil ocupar índice con filtro
Si bien los índices con filtro ocupan menos espacio y en general pueden ocupar menos recursos y tiempo cuando se hace uso de ellos, la principal desventaja que se tiene es que solo funciona para un subconjunto de los datos, así es que ¿qué tan bueno es crearlos? La respuesta es que depende.
- Depende del número de registros, mientras más registros haya en la tabla podría ser útil considerar usarlos.
- Depende del campo o campos que se usarán en el WHERE, ya que si -en el ejemplo propuesto- un 90% de los registros son de la entidad 10 entonces no se vería una reducción en tiempo, costo y espacio al utilizar un índice condicional.
- Depende de los querys que se hagan sobre la tabla; es decir, ¿qué tanto se hacen consultas sobre la entidad=10 del ejemplo anterior? ¿es muy común filtrar por esa entidad, pero también por las demás?