El índice de una base de datos es una estructura alternativa de los datos en una tabla. El propósito de los índices es acelerar el acceso a los datos mediante operaciones físicas más rápidas y efectivas. En pocas palabras, se mejoran las operaciones gracias a un aumento de la velocidad, permitiendo un rápido acceso a los registros de una tabla en una base de datos. Al aumentar drásticamente la velocidad de acceso, se suelen usar sobre aquellos campos sobre los cuáles se hacen búsquedas frecuentes.
Índices
Un índice es una estructura opcional, asociado con
una mesa o tabla de clúster, que a veces puede acelerar el acceso de datos.
Mediante la creación de un índice en una o varias columnas de una tabla, se
obtiene la capacidad en algunos casos, para recuperar un pequeño conjunto de
filas distribuidas al azar de la tabla. Los índices son una de las muchas
formas de reducir el disco I / O.
Si una tabla de montón organizado no tiene
índices, entonces la base de datos debe realizar un escaneo completo de tabla
para encontrar un valor.
Características de Indexación.
Los índices son objetos de esquema que son lógica
y físicamente independiente de los datos de los objetos con los que están
asociados. Por lo tanto, un índice se puede quitar o creado sin afectar físicamente
a la tabla para el índice.
Los índices tienen las
siguientes propiedades:
Facilidad de Uso.
Los índices son utilizables (por defecto) o
inutilizable. Un índice inutilizable no se mantiene por las operaciones DML y
es ignorado por el optimizador. Un índice inutilizable puede mejorar el
rendimiento de las cargas a granel. En lugar de dejar un índice y luego
volverlo a crear, puede hacer que el índice inservible y luego reconstruirlo.
Índices inutilizables y las particiones de índice no consumen espacio. Cuando
usted hace un índice utilizable no utilizable, la base de datos cae su segmento
de índice.
·Visibilidad.
Los índices son visibles (por defecto) o
invisible. Un índice invisible se mantiene por las operaciones DML y no se
utiliza de forma predeterminada por el optimizador. Cómo hacer una invisible
índice es una alternativa a lo que es inutilizable o se caiga. Índices
invisibles son especialmente útiles para probar la eliminación de un índice
antes de dejarlo caer o mediante índices temporalmente sin afectar a la
aplicación general.
Teclas y Columnas.
Una clave es un conjunto de columnas o expresiones
en las que se puede construir un índice. Aunque los términos se usan
indistintamente, los índices y las claves son diferentes. Los índices son
estructuras almacenados en la base de datos que los usuarios a administrar el
uso de sentencias de SQL. Las claves son estrictamente un concepto lógico.
La siguiente sentencia crea un índice en la
columna customer_id de la muestra oe.orders tabla:
CREATE INDEX ord_customer_ix ON orders
(customer_id);
En la declaración anterior, la columna customer_id
es la clave de índice. El índice en sí se llama ord_customer_ix.
Índices Compuestos.
Un índice compuesto, también llamado índice concatenado,
es un índice de varias columnas de una tabla. Las columnas de un índice
compuesto que deben aparecer en el orden que tenga más sentido para las
consultas que recuperar datos y no necesita ser adyacente en la tabla.
Los índices compuestos pueden acelerar la
recuperación de datos para las instrucciones SELECT en la que el DONDE
referencias cláusula totalidad o la parte principal de las columnas en el
índice compuesto. Por lo tanto, el orden de las columnas utilizadas en la
definición es importante. En general, las columnas de acceso más común van
primero.
Por ejemplo, supongamos que una aplicación realiza
consultas frecuentes a apellidos, job_id, y columnas de salario en la tabla
empleados. También asumir que last_name tiene alta cardinalidad, lo que
significa que el número de valores distintos que es grande en comparación con
el número de filas de la tabla. Se crea un índice con el siguiente orden de las
columnas:
CREATE INDEX employees_ix
ON employees (last_name, job_id, salary);
Las consultas que acceden a las tres columnas,
sólo la columna last_name, o sólo el last_name y columnas job_id utilizan este
índice. En este ejemplo, las consultas que no tienen acceso a la columna
last_name no utilizan el índice.
Índices Únicos y no Únicos.
Los índices pueden ser únicos o no únicos. Índices
únicos garantizar que no hay dos filas de una tabla tienen valores duplicados
en la columna de clave o columna. Por ejemplo, dos empleados no pueden tener el
mismo ID de empleado. Por lo tanto, en un índice único, existe una ROWID para
cada valor de datos. Los datos de los bloques de hojas se ordenan sólo por
clave.
Índices no únicos permiten valores duplicados en
la columna o columnas indexadas. Por ejemplo, la columna 'nombre de la tabla de
empleados puede contener varios valores Mike. Para un índice no único, el ROWID
se incluye en la clave de forma ordenada, por lo que los índices no únicos se
ordenan por la clave de índice y ROWID (ascendente).
Oracle Database no filas de la tabla de índice en
el que todas las columnas clave son nulas, a excepción de los índices de mapa
de bits o cuando el valor de la columna clave de clúster es nulo.
Tipos de Índices.
Base de Datos Oracle ofrece varias combinaciones
de indexación, que proporcionan una funcionalidad complementaria sobre el
rendimiento. Los índices se pueden clasificar de la siguiente manera:
Los Índices de Árbol B.
Estos índices son el tipo de índice estándar. Son
excelentes para la clave principal y los índices altamente selectivos.
Utilizado como índices concatenados, B-tree índice pueden recuperar los datos
ordenados por las columnas de índice. Índices B-tree tienen los siguientes
subtipos:
Índice de Tablas Organizadas.
Una tabla de índice-organizada difiere de un
montón-organizado porque los datos son en sí mismo el índice.
En este tipo de índice, los bytes de la clave de
índice se invierten, por ejemplo, 103 se almacena como 301. La inversión de
bytes extiende inserta en el índice durante muchos bloques.
·Índices Descendentes.
Este tipo de índice almacena los datos en una
columna o columnas de concreto en orden descendente.
Índices B-Tree de Racimo.
Este tipo de índice se utiliza para indexar una
clave de clúster tabla. En lugar de apuntar a una fila, los puntos clave para
el bloque que contiene filas relacionadas con la clave de clúster.
Mapa de Bits y los Índices Bitmap Join.
En un índice de mapa de bits, una entrada de
índice utiliza un mapa de bits para que apunte a varias filas. En cambio, los
puntos de entrada de un índice B-tree en una sola fila. Un índice de
combinación de mapa de bits es un índice de mapa de bits para la unión de dos o
más tablas. Consulte "Indicadores de mapa de bits".
Índices Basados en Funciones.
Este tipo de índice incluye columnas que, o bien
se transforman por una función, tales como la función UPPER, o incluidos en una
expresión. Índices B-tree o mapa de bits puede ser basado en las funciones.
·Índices de Dominio de Aplicación.
Este tipo de índice se crea por un usuario para
los datos en un dominio específico de la aplicación. El índice físico no tiene
que utilizar una estructura de índice tradicional y se puede almacenar ya sea
en la base de datos Oracle como tablas o externamente como un archivo. Consulte
"Indicadores de dominio de aplicación".
Índices B-Tree.
Árboles B, abreviatura de árboles balanceados, son el tipo más común de índice de base de datos. Un índice B-tree es una lista ordenada de valores dividida en rangos. Mediante la asociación de una tecla con una fila o rango de filas, los árboles B proporcionan un excelente rendimiento de la recuperación para una amplia gama de consultas, incluyendo coincidencia exacta y búsquedas por rango.
Los índices agrupados, definen el orden en que
almacenan las filas de la tabla (nodos hoja/página de datos de la imagen
anterior). La clave del índice agrupado es el elemento clave para esta
ordenación; el índice agrupado se implementa como una estructura de árbol b que
ayuda a que la recuperación de las filas a partir de los valores de las claves
del índice agrupado sea más rápida. Las páginas de cada nivel del índice,
incluidas las páginas de datos del nivel hoja, se vinculan en una lista con
vínculos dobles. Además, el desplazamiento de un nivel a otro se produce
recorriendo los valores de claves.
Los índices no agrupados tienen la misma
estructura de árbol b que los índices agrupados, con algunos matices; como
hemos visto antes, en los índices agrupados, en el último nivel del índice
(nivel de hoja) están los datos; en los índices no-agrupados, en el nivel de
hoja del índice, hay un puntero a la localización física de la fila
correspondiente en el índice agrupado. Además, la ordenación de las filas del
índice está construida en base a la(s) columna(s) indexadas, lo cual no quiere
decir (a diferencia de los índices agrupados), que la organización física de
las páginas de datos corresponda con el índice.
Reorganización.
Un factor clave para conseguir una E/S de disco
mínima para todas las consultas de bases de datos es asegurarse de que se creen
y se mantengan buenos índices. Una vez creados los índices, se debe procurar
mantenerlos para asegurarse que sigan trabajando en forma óptima. A medida que
se agregan, modifican o borran datos se produce fragmentación. Esta
fragmentación puede ser buena o mala para el rendimiento del sistema,
dependiendo de las necesidades del trabajo de la base de datos.
Fragmentación de los Índices.
La fragmentación es consecuencia de los procesos
de modificación de los datos (instrucciones INSERT, UPDATE y DELETE) efectuados
en la tabla y en los índices definidos en la tabla. Como dichas modificaciones
no suelen estar distribuidas de forma equilibrada entre las filas de la tabla y
los índices, el llenado de cada página puede variar con el paso del tiempo.
Para las consultas que recorren parcial o totalmente los índices de una tabla,
este tipo de fragmentación puede producir lecturas de páginas adicionales. Esto
impide el recorrido paralelo de los datos. Existen dos tipos de fragmentación:
Interna: Fragmentación dentro de páginas individuales de
datos e índices con espacios libres que generan la necesidad de más operaciones
de E/S y más memoria para su lectura. Este hecho disminuye el rendimiento en
ambientes de lectura, pero en algunos casos puede beneficiar las inserciones,
que no requieren una división de páginas con tanta frecuencia.
Externa: Cuando el orden lógico de las páginas no es
correcto, porque las páginas no son contiguas. El acceso a los datos es mucho
más lento por la necesidad de búsqueda de los datos.
La fragmentación de índices se puede reparar
reorganizando un índice o reconstruyéndolo. Para los índices fraccionados que
fueron construidos en una estructura partida se puede usar cualquiera de estos
métodos o bien en un índice completo o bien en un único fragmento del índice.
Detección de Fragmentación.
El primer paso para decidir qué método de
desfragmentación se va a utilizar consiste en analizar el índice para
determinar el nivel de fragmentación. Si se usa la función del sistema
sys.dm_db_index_physical_stats, se puede detectar la fragmentación de los
índices de la base de datos thuban-homologada.
SELECT
DISTINCT
A.INDEX_ID
'IDIndice';
sys.TABLES.name
'Tabla',
b.name
'Indice',
avg_fragmentation_in_percentr
'% Fragmentación',
fragment_count
'Cantidad de Fragmentos',
avg_fragment_size_in_pages
'Promedio de fragmentos por página',
FROM
sys.dm_db_index_physical_stats
(
DB_ID
()N'thuban-himologada'),
OBJECT_ID
(N'dbo.*'),
NULL,
NULL,
NULL) AS
a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =
b.index_id,
sys.TABLES
WHERE
sys.TABLES.object_id
= b.object_id
ORDER BY
avg_fragmentation_in_percent
DESC
La grilla de resultados emitida por la anterior sentencia incluye las siguientes columnas:
Una vez que
se toma conciencia del nivel de fragmentación, se debe utilizar la tabla a
continuación para determinar el mejor método para su corrección.
La
reconstrucción del índice puede ejecutarse tanto en línea como fuera de línea.
La reorganización de los índices debe ejecutarse siempre en línea. Para
adquirir una disponibilidad similar a la de la opción de reorganización, los
índices deben ser reconstruidos en línea.
Estos
valores proveen una estricta guía para determinar el punto en el que se debe
cambiar de ALTER INDEX REORGANIZE a ALTER INDEX REBUILD.
Los niveles
muy bajos de fragmentación (menores que el 5 por ciento) no deben ser
corregidos por ninguno de estos comandos porque el beneficio de la remoción de
una cantidad tan pequeña de fragmentación es casi siempre superado ampliamente
por el costo de reorganización o reconstrucción de índices.
Reorganización de Índices.
Para reorganizar uno o más índices se debe usar la
sentencia ALTER INDEX con la cláusula REORGANIZE. Por ejemplo:
ALTER INDEX
PK_LOGS ON THUBAN_LOGS REORGANIZE
El proceso de reorganización de índices se realiza siempre en línea y el consumo de recursos es bajo por lo que no mantiene bloqueos por mucho tiempo.
Reconstrucción:
Es importante periódicamente examinar y determinar
qué índices son susceptibles de ser reconstruidos. Cuando un índice está
descompensado puede ser porque algunas partes de éste han sido accedidas con
mayor frecuencia que otras. Como resultado de este suceso podemos obtener
problemas de contención de disco o cuellos de botella en el sistema.
Normalmente reconstruimos un índice con el comando ALTER INDEX.
Es importante tener actualizadas las estadísticas
de la base de datos. Para saber si las estadísticas se están lanzando
correctamente podemos hacer una consulta sobre la tabla dba_indexes y ver el
campo last_analyzed para observar cuando se ejecutaron sobre ese índice las
estadísticas.
Blevel (branch level) es parte del formato del
B-tree del índice e indica el número de veces que Oracle ha tenido que reducir
la búsqueda en ese índice. Si este valor está por encima de 4 el índice deberá
de ser reconstruido.
ALTER INDEX <index_name> REBUILD;
Para reconstruir una partición de un índice
podríamos hacer los siguiente:
ALTER INDEX <index_name> REBUILD PARTITION <nb_partition> NOLOGGING;
No hay comentarios:
Publicar un comentario