ÍNDICES
Introducción 1. ÍNDICES 1.1 Tipos de Índices 1.1.1 Tipos de índices en ORACLE 1.1.1.1 Índices B-Tree 1.1.1.2 Índices Bitmap 1.1.2 Tipos de índices en SQL SERVER 1.1.2.1 Índices Agrupados 1.1.2.2 Índices No Agrupados 1.1.3 Tipos de índices en POSTGRESQL 1.1.3.1 Vacuum Verbose Analyze 1.1.3.2 Fulltextindex RECURSOS BIBLIOGRÁFICOS GLOSARIO
FAVA - Formación en Ambientes Virtuales de Aprendizaje
3 3 4 5 5 6 7 7 8 9 9 9 10 11
SENA - Servicio Nacional de
Aprendizaje
l a u t p s e e c c i n o d c n Í a p a M
2 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
ÍNDICES INTRODUCCIÓN Asegurar el funcionamiento óptimo de las bases de datos depende en gran medida de tener procesos definidos y gestionados que se orienten prioritariamente a la disponibilidad de los datos. Es de mucho interés la forma como están dispuestos los diferentes archivos que integran todo el sistema y con ello también las acciones encaminadas a obtener la información para las diferentes tareas de una organización de forma rápida y segura. Existen muchas formas de configuración y creación de archivos, los índices aparecen como respuesta a la necesidad de poder acceder a los datos de forma ágil y previendo situaciones que tienen que ver con: planes de recuperación de la información, restauración ante problemas derivados de desastres y demás actividades que un administrador de bases de datos debe poder controlar y gestionar. Enfocados en estas tareas, nos ocuparemos de estudiar las diferentes opciones de índices que se podrían tener creados y administrados en motores de bases de datos como: Oracle, Sql Server y PostgreSQL.
1. ÍNDICES Los índices son estructuras asociadas con las tablas o vistas que se crean para mejorar el rendimiento inherente a procesos de actualización y recuperación de datos, proporcionando además una ruta de acceso directo a registros. Los índices se pueden crear asociados a uno o más campos de una tabla, teniendo en cuenta que una vez se crean, son automáticamente mantenidos y utilizados en la base de datos.
3 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
Las operaciones asociadas a una tabla, como adicionar nuevos registros, actualización de datos, o eliminación de registros, se reflejan automáticamente en todos los índices y este tipo de acciones son originadas con total transparencia a los usuarios. Los índices también mejoran el rendimiento en la definición de llaves primarias y las restricciones de claves únicas. Sin la presencia de índices, una tabla en una base de datos, es totalmente escaneada con cada operación DML efectuada.
Indexes Where key = 22 Row Key pointer
22 22 Index
Table
1.1 Tipos de Índices Los índices están estructurados para facilitar una respuesta rápida en un conjunto de resultados, teniendo la opción de crear diferentes tipos que dependen de las necesidades y del motor de base de datos con el que se esté operando. A continuación estudiaremos algunos tipos de índices analizando situaciones puntuales asociadas a los motores de bases de datos Oracle, Sql Server y PostgreSQL.
4 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
1.1.1 Tipos de índices en ORACLE 1.1.1.1 Índices B-Tree El índice B-Tree es el tipo de índice más común en una base de datos Oracle. Es el índice default, es decir que si no se específica el tipo al momento de crearlo, Oracle lo asigna como B-Tree.
Root
Branch Leaf
Index entry Index entry header Key column length Key column value ROWID Un índice B-tree tiene sus valores clave almacenados en un árbol equilibrado, lo que permite una realización de búsquedas binarias rápidas. La estructura de un índice B-tree se define así: la parte superior del índice es la raíz, que a su vez contiene las entradas que apuntan al siguiente nivel en el índice. El siguiente nivel contiene los bloques denominados ramas que apuntan a los bloques del siguiente nivel en el índice. En el nivel más bajo encontramos los nodos denominados hojas que contienen las entradas del índice que apuntan a los registros de la tabla. Las hojas están doblemente enlazadas para facilitar el análisis del índice en un orden ascendente, así como descender por los valores clave.
5 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
1.1.1.2 Índices Bitmap Este índice tiene un mapa de bits para cada valor clave distinto que esté siendo indexado. En cada caso hay un conjunto de bits por cada registro en la tabla que se esté indexando. Esto permite búsquedas rápidas cuando hay pocos valores distintos, es decir, el campo indexado tiene cardinalidad baja.
File 3 Block 10
Block 11
Block 12
Star
End
Key
ROWID
ROWID
Bitmap
10.0.3,
12.8.3,
1000100100010010100>
10.0.3,
12.8.3,
0001010000100100000>
10.0.3,
12.8.3,
0100000011000001001>
10.0.3,
12.8.3,
0010001000001000010>
Un ejemplo que podríamos mencionar es el asociado al campo género que tiene valores “M” y “F” manejando dos mapas de bits para la búsqueda. Si un índice de mapa de bits se utiliza en un campo “numero_telefono”, existirían muchos mapas de bits para administrar y la búsqueda se torna ineficiente. Los índices de mapa de bits pueden ser una mejor opción que los B-tree en situaciones como:
6 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
•Cuando una tabla tiene millones de registros y los campos clave
tienen cardinalidad baja (Hay pocos valores distintos en la columna).
•Cuando las consultas suelen manejar múltiples condiciones en el
“WHERE” con presencia de operadores lógicos como “OR”.
•Cuando los campos definidos como claves se definen de solo
lectura o con mínimas actividades de actualización.
Un índice Bitmap es organizado como un índice B-tree, pero los nodos representados en las hojas almacenan un mapa de bits por cada valor clave en lugar de una lista de IDs de fila. Cada bit en el mapa de bit corresponde a un ID de fila y si el bit es un conjunto, significa que el registro con el correspondiente ID de fila contiene el valor clave. Como se puede apreciar en la figura 3, el nodo hoja de un índice Bitmap contiene lo siguiente: •Una cabecera de entrada que contiene el número de campos y la
información de bloqueo.
•Valores claves que contienen la longitud y valores pares de cada
campo clave.
1.1.2 Tipos de índices en SQL SERVER 1.1.2.1 Índices Agrupados Este tipo de índice es particularmente eficiente para consultas, dado que los registros de datos completos (en páginas de datos) son guardados a nivel de hoja del B-tree. El ordenamiento y la ubicación de los datos en un índice agrupado son similares al de un diccionario donde las palabras son ordenadas en forma alfabética y las definiciones aparecen junto a las palabras. Cuando se crea una restricción PRIMARY KEY en una tabla que no contiene un índice agrupado, Sql Server creará uno y utiliza la columna de clave primaria como clave para el índice agrupado. Si ya existe un índice agrupado Sql Server creará un índice no agrupado sobre la columna definida con una restricción PRIMARY KEY. Una columna definida como la clave primaria es un índice muy útil porque los valores de la columna están garantizados que son únicos. Índices sobre columnas de valores únicos son de menor tamaño que los índices sobre columnas con valores duplicados y generan estructuras de búsqueda más eficientes.
7 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
Una columna definida con una restricción UNIQUE genera automáticamente un índice no agrupado. Para forzar el tipo de índice a ser creado para una columna o columnas, se puede especificar las cláusulas CLUSTERED o NONCLUSTERED en los comando CREATE TABLE, ALTER TABLE o CREATE INDEX. Como ejemplo vamos a suponer que se crea una tabla Empleado que contiene los campos: EmpleadoID, Nombre, Apellidos y NumDocumento. El campo EmpleadoID se define con la restricción PRIMARY KEY, el campo NumDocumento con la restricción UNIQUE. Para hacer un índice agrupado para el campo NumDocumento y un índice no agrupado para el campo EmpleadoID, podemos usar la siguiente sintaxis: CREATE TABLE dbo.Empleado ( EmpleadoID int PRIMARY KEY NONCLUSTERED, Nombre varchar(40), Apellidos varchar(40), NumDocumento char(15) UNIQUE CLUSTERED ) Los índices no se limitan a las restricciones. Se pueden crear índices sobre cualquier campo o combinación de campos en una tabla. Los índices agrupados aseguran la unicidad internamente, por tanto si se crea un índice agrupado sobre campos con valores no únicos, Sql Server crea un único valor sobre los campos duplicados para servir de clave de ordenamiento secundaria. 1.1.2.2 Índices No Agrupados Un índice no agrupado es similar a un índice al final de un libro, se puede usar para ubicar las páginas que contienen un tema en el índice del libro. La base de datos usa los índices no agrupados para encontrar registros según una clave. Al igual que los índices agrupados (clustered), los no agrupados (non clustered) son estructuras tipo B-tree. Un índice no agrupado es una estructura de índice separada, independiente del ordenamiento físico de los registros en la tabla. Si existe un índice agrupado en una tabla, un índice no agrupado utilizará al índice agrupado para la búsqueda de los registros. En la mayoría de los casos se creará antes un índice agrupado que los índices no agrupados sobre una tabla.
8 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
1.1.3 Tipos de índices en POSTGRESQL 1.1.3.1 Vacuum Verbose Analyze PostgreSQL antes de crear un índice, hace un poco de heurística para determinar qué tipo de estructura de datos utilizará y que sea óptima para un campo o conjunto de campos. Si la información de la base de datos se consultará con frecuencia sobre un atributo, ese es el candidato ideal para ser indexado, y como en todos los motores de bases de datos, en el caso de una llave primaria, PostgreSQL automáticamente crea el índice. Una forma de conocer que campos conviene indexar, es correr un vacuum verbose analyze sobre la base de datos para actualizar las estadísticas de las tablas y luego utilizar la instrucción explain sobre las consultas que sabemos serán las más frecuentes. 1.1.3.2 Fulltextindex Almacenar grandes textos en una base de datos no es muy recomendado, como tampoco lo es crear índices sobre este tipo de campos dado que las búsquedas pueden llegar a efectuarse con el mismo contenido del campo. Sin embargo puede presentarse el caso donde realmente sea necesario tener un campo así y en el cual se deba crear un índice, y es entonces cuando se puede recurrir a la función fulltextindex que básicamente lo que hace es extraer las palabras y las referencias del registro de una tabla auxiliar donde las palabras están indexadas, de tal forma que cuando se quiere formular una búsqueda sobre el campo de texto, se buscan las palabras en esa tabla y lo que regresan son las referencias de las tuplas donde aparecen generando un ahorro significativo en espacio y una ganancia en velocidad.
9 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
RECURSOS BIBLIOGRÁFICOS • Abraham Silderschatz, Henry F. Korth, S Sadarshan(2006). Fundamentos de bases de datos. McGRAW-HILL.
• Dolores Cuadra, Elena Castro, Ana Iglesias, Paloma Martínez, Javier Calle, Cesar De Pablo, Harith Al-Jumaily, Lourdes Moreno(2008). Desarrollo de Bases de Datos: Casos prácticos desde el análisis de a la implementación. Alfaomega, RA-MA.
• Maria Billings, Deirdre Matishak, Jim Spiller, Priya Vennapusa(2008). Oracle Database 11g: Administration
Workshop I.
• James Womack, Tom Best, Donna Keesling, James Spliller, Maria Billings, Gwen Lazenby (2009). Oracle Database 11g:
Administration Workshop II. Webgrafía
• Página recuperada el 28 de mayo de 2012, de Índices B-Tree :
http://sqleficiente.wordpress.com/2008/07/19/indices-b-tree/.
• Página recuperada el 28 de mayo de 2012, de Manual de SQL para Oracle: http://www.jorgesanchez.net/bd/oracleSQL.pdf.
10 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
GLOSARIO B-TREE:
Índice más común en una base de datos Oracle
VACUUM VERBOSE ANALYZE :
Proceso que al ser ejecutado en PostgreSQL permite conocer que campos conviene indexar.
11 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje
Indices
OBJETO DE APRENDIZAJE
Indices
Desarrollador de contenido Experto temático
Fernelly Carvajal Silva
Asesor Pedagógico
Rafael Neftalí Lizcano Reyes
Productor Multimedia
José Jaime Luis Tang Pinzón Victor Hugo Tabares Carreño
Programadores
Daniel Eduardo Martínez Díaz Francisco José Lizcano
Líder expertos temáticos
Ana Yaqueline Chavarro Parra
Líder línea de producción
Santiago Lozada Garcés
Atribución, no comercial, compartir igual Este material puede ser distribuido, copiado y exhibido por terceros si se muestra en los créditos. No se puede obtener ningún beneficio comercial y las obras derivadas tienen que estar bajo los mismos términos de licencia que el trabajo original.
12 FAVA - Formación en Ambientes Virtuales de Aprendizaje
SENA - Servicio Nacional de
Aprendizaje