Unidad 4
Operación y mantenibilidad
4.1 Bitácoras de Trabajo del DBMS
Una bitácora (log) es una herramienta
(archivos o registros) que permite registrar, analizar, detectar y notificar
eventos que sucedan en cualquier sistema de información utilizado en las
organizaciones.
La estructura más ampliamente usada
para grabar las acciones que se llevan en la base de datos.
Nos ayuda a recuperar la información
ante algunos incidentes de seguridad, detección de comportamiento inusual,
información para resolver problemas, evidencia legal, es de gran ayuda en las
tareas de computo forense.
Permite guardar las transacciones
realizadas sobre una base de datos en específico, de tal manera que estas
transacciones puedan ser auditadas y analizadas posteriormente.
Pueden obtenerse datos específicos de
la transacción como:
1.
Operación que se realizó
2.
Usuario de BD
3.
Fecha
4.
Máquina
5.
Programa
6.
Tipo de conexión
7.
Estado
No se requiere hacer cambios en los
sistemas de producción o de desarrollo o en una simple instalación para la
implementación de la bitácora.
A través de la parametrización se
generan las pantallas de consulta y reportes sin necesidad de programar.
Acceso a la bitácora a través de una
aplicación Web.
Control de Acceso a la información de
la bitácora a través de Roles.
Se puede implementar en los sistemas
de información que utilicen las principales bases de datos: Oracle, SQL Server,
Informix, Sybase.
Permite hacer el seguimiento de todos
los cambios que ha tenido un registro.
4.1.1 Funciones
Específicas de las Bitácoras
La estructura más ampliamente usada
para grabar las modificaciones de la base de datos es la Bitácora. Cada
registro de la bitácora escribe una única escritura de base de datos y tiene lo
siguiente:
·
Nombre
de la Transacción
Valor antiguo
Valor Nuevo
Es fundamental que siempre se cree un
registro en la bitácora cuando se realice una escritura antes de que se
modifique la base de datos.
También tenemos la posibilidad de
deshacer una modificación que ya se ha escrito en la base de datos, esto se
realizará usando el campo del valor antiguo de los registros de la bitácora.
Los registros de la bitácora deben
residir en memoria estable como resultado el volumen de datos en la bitácora
puede ser exageradamente grande.
Las operaciones COMMIT y ROLLBACK
establecen lo que se le conoce como punto de sincronización lo cual representa
el límite entre dos transacciones consecutivas, o el final de una unidad lógica
de trabajo, y por tanto al punto en el cual la base de datos esta (o debería
estar) en un estado de consistencia. Las únicas operaciones que establecen un
punto de sincronización son COMMIT, ROLLBACK y el inicio de un programa. Cuando
se establece un punto de sincronización:
Se comprometen o anulan todas las
modificaciones realizadas por el programa desde el punto de sincronización
anterior.
Se pierde todo posible posicionamiento
en la base de datos. Se liberan todos los registros bloqueados. Es importante
advertir que COMMIT y ROLLBACK terminan las transacción, no el programa.
4.1.2 Recuperación
(Rollback)
En tecnologías de base de datos, un
rollback es una operación que devuelve a la base de datos a algún estado
previo. Los Rollbacks son importantes para la integridad de la base de datos, a
causa de que significan que la base de datos puede ser restaurada a una copia
limpia incluso después de que se han realizado operaciones erróneas. Son
cruciales para la recuperación de crashes de un servidor de base de datos;
realizando rollback (devuelto) cualquier transacción que estuviera activa en el
tiempo del crash, la base de datos es restaurada a un estado consistente.
En SQL, ROLLBACK es un comando que
causa que todos los cambios de datos desde la última sentencia BEGIN WORK, o
START TRANSACTION sean descartados por el sistema de gestión de base de datos
relacional (RDBMS), para que el estado de los datos sea "rolled
back"(devuelto) a la forma en que estaba antes de que aquellos cambios
tuvieran lugar.
Una sentencia ROLLBACK también
publicará cualquier savepoint existente que puediera estar en uso.
En muchos dialectos de SQL, los
ROLLBACK son específicos de la conexión. Esto significa que si se hicieron dos
conexiones a la misma base de datos, un ROLLBACK hecho sobre una conexión no
afectará a cualesquiera otras conexiones. Esto es vital para el buen funcionamiento
de la Concurrencia.
La funcionalidad de rollback está
normalmente implementada con un Log de transacciones, pero puede también estar
implementada mediante control de concurrencia multiversión.
4.1.3 Permanencia
(Commit)
En el contexto de la Ciencia de la
computación y la gestión de datos, commit (acción de comprometer) se refiere a
la idea de consignar un conjunto de cambios "tentativos, o no
permanentes". Un uso popular es al final de una transacción de base de
datos.
Una sentencia COMMIT en SQL finaliza
una transacción de base de datos dentro de un sistema gestor de base de datos
relacional (RDBMS) y pone visibles todos los cambios a otros usuarios. El
formato general es emitir una sentencia BEGIN WORK, una o más sentencias SQL, y
entonces la sentencia COMMIT. Alternativamente, una sentencia ROLLBACK se puede
emitir, la cual deshace todo el trabajo realizado desde que se emitió BEGIN
WORK. Una sentencia COMMIT publicará cualquiera de los savepoints (puntos de
recuperación) existentes que puedan estar en uso.
En términos de transacciones, lo
opuesto de commit para descartar los cambios "en tentativa" de una
transacción, es un rollback.
4.2 Definición de los
Modos de Operación de un DBMS (Alta, Baja, Recovery)
La vida de todo archivo comienza
cuando se crea y acaba cuando se borra. Durante su existencia es objeto de
constante procesamiento, que con mucha frecuencia incluye acciones de consulta
o búsqueda y de actualización. En el caso de la estructura archivos,
entenderemos como actualización, además de las operaciones, vistas para
vectores y listas enlazadas, de introducir nuevos datos (altas) o de eliminar
alguno existente (bajas), la modificación de datos ya existentes, (operación
muy común con datos almacenados). En esencia, es la puesta al día de los datos
del archivo.
Una operación de alta en un archivo
consiste en la adición de un nuevo registro. En un archivo de empleados, un
alta consistirá en introducir los datos de un nuevo empleado. Para situar
correctamente un alta, se deberá conocer la posición donde se desea almacenar
el registro correspondiente: al principio, en el interior o al final de un
archivo.
El algoritmo de ALTAS debe contemplar
la comprobación de que el registro a dar de alta no existe previamente. Una
baja es la acción de eliminar un registro de un archivo. La baja de un registro
puede ser lógica o física. Una baja lógica supone el no borrado del registro en
el archivo. Esta baja lógica se manifiesta en un determinado campo del registro
con una bandera, indicador o “flag” -carácter *. $, etc.,-, o bien con la
escritura o rellenado de espacios en blanco en el registro dado de baja
Altas
La operación de dar de alta un
determinado registro es similar a la de añadir datos a un archivo. Es
importante remarcar que en un archivo secuencial sólo permite añadir datos al
final del mismo.
En otro caso, si se quiere insertar un
registro en medio de los ya presentes en el archivo, sería necesaria la
creación nueva del archivo.
El algoritmo para dar de alta un
registro al final del fichero es como sigue:
algoritmo altas
leer registro de alta
inicio
abrir archivo para añadir
mientras haya más registros hacer
{algunos lenguajes ahorran este bucle}
leer datos del registro
fin_mientras
escribir (grabar) registro de alta en
el archivo
cerrar archivo
fin
Bajas
Existen dos métodos para dar de baja a
un registro en un archivo secuencial, donde no es fácil eliminar un registro
situado en el interior de una secuencia: Para ello podemos seguir dos métodos:
1) Utilizar y por tanto crear un
segundo archivo auxiliar transitorio, también secuencial, copia del que se
trata de actualizar. Se lee el archivo completo registro a registro y en
función de su lectura se decide si el registro se debe dar de baja o no. En
caso afirmativo, se omite la escritura en el archivo auxiliar. Si el registro
no se va a dar de baja, este registro se reescribe en el archivo auxiliar
Tras terminar la lectura del archivo
original, se tendrán dos archivos: original (o maestro) y auxiliar. El proceso
de bajas del archivo concluye borrando el archivo original y cambiando el
nombre del archivo auxiliar por el del inicial.
2) Guardar o señalar los registros que
se desean dar de baja con un indicador o bandera que se guarda en un array; de
esta forma los registros no son borrados físicamente, sino que son considerados
como inexistentes.
Inevitablemente, cada cierto tiempo,
habrá que crear un nuevo archivo secuencial con el mismo nombre, en el que los
registros marcados no se grabarán.
Propósito de Backup y
Recuperación
Como administrador de copia de
seguridad, la tarea principal es diseñar, implementar y gestionar una
estrategia de backup y recuperación. En general, el propósito de una estrategia
de recuperación de copia de seguridad y es para proteger la base de datos
contra la pérdida de datos y reconstruir la base de datos después de la pérdida
de datos. Normalmente, las tareas de administración de seguridad son las
siguientes:
ü
Planificación
y probar las respuestas a diferentes tipos de fallas.
ü
Configuración
del entorno de base de datos de copia de seguridad y recuperación.
ü
La
creación de un programa de copia de seguridad
ü
Seguimiento
de la copia de seguridad y entorno de recuperación
ü
Solución
de problemas de copia de seguridad
ü
Para
recuperarse de la pérdida de datos en caso de necesidad
Como administrador de copia de
seguridad, es posible que se le pida que realice otros deberes que se
relacionan con copia de seguridad y recuperación:
·
La
preservación de datos, lo que implica la creación de una copia de base de datos
para el almacenamiento a largo plazo
·
La
transferencia de datos, lo que implica el movimiento de datos de una base de
datos o un host a otro.
De Protección de
Datos
Como administrador de copia de
seguridad, su trabajo principal es hacer copias de seguridad y vigilancia para
la protección de datos. Una copia de seguridad es una copia de los datos de una
base de datos que se puede utilizar para reconstruir los datos. Una copia de
seguridad puede ser una copia de seguridad física o una copia de seguridad
lógica.
Copias de seguridad físicas son copias
de los archivos físicos utilizados en el almacenamiento y la recuperación de
una base de datos. Estos archivos incluyen archivos de datos, archivos de
control y los registros de rehacer archivados. En última instancia, cada copia
de seguridad física es una copia de los archivos que almacenan información de
base de datos a otra ubicación, ya sea en un disco o en medios de
almacenamiento fuera de línea, tales como cinta.
Copias de seguridad lógicas contienen
datos lógicos, como tablas y procedimientos almacenados. Puede utilizar Oracle
Data Pump para exportar los datos a archivos lógicos binarios, que
posteriormente puede importar a la base de datos. Clientes de línea de comandos
La bomba datos expdp y impdp utilizan el DBMS_DATAPUMP y DBMS_METADATA PL / SQL
paquetes.
Copias de seguridad físicas son la
base de cualquier estrategia de recuperación de copia de seguridad sólida y.
Copias de seguridad lógicas son un complemento útil de las copias de seguridad
físicas en muchas circunstancias, pero no son suficiente protección contra la
pérdida de datos y sin respaldos físicos.
A menos que se especifique lo
contrario, la copia de seguridad término tal como se utiliza en la copia de
seguridad y la documentación de recuperación se refiere a una copia de
seguridad física. Copia de seguridad de una base de datos es el acto de hacer
una copia de seguridad física. El enfoque en la copia de seguridad y
recuperación de documentación está casi exclusivamente en copias de seguridad
físicas.
Mientras que varios problemas pueden
detener el funcionamiento normal de una base de datos Oracle o afectar a las
operaciones de base de datos de E / S, solamente la siguiente normalmente
requiere la intervención del DBA y de recuperación de datos: un error de
medios, errores de usuario, y los errores de aplicación. Otros fallos pueden
requerir intervención DBA sin causar la pérdida de datos o que requieren la
recuperación de copia de seguridad. Por ejemplo, es posible que tenga que
reiniciar la base de datos tras un fallo de instancia o asignar más espacio de
disco después de un fallo debido a la declaración de un archivo de datos
completo.
Las Fallas de Medios
La falta de medios es un problema
físico con un disco que provoca un fallo de una leer o escribir en un archivo
de disco que se requiere para ejecutar la base de datos. Cualquier archivo de
base de datos puede ser vulnerable a un fallo de comunicación. La técnica de
recuperación adecuada después de un fallo de los medios de comunicación depende
de los archivos afectados y el tipo de copia de seguridad disponible.
Un aspecto particularmente importante
de la copia de seguridad y recuperación se está desarrollando una estrategia de
recuperación ante desastres para proteger contra la pérdida de datos
catastrófica, por ejemplo, la pérdida de toda una serie de bases de datos.
Errores de los
Usuarios
Los errores del usuario cuando se
producen, ya sea debido a un error en la lógica de la aplicación o un error
manual, los datos en una base de datos se modifican o eliminan incorrectamente.
Errores de usuario se estima que la mayor causa de inactividad de base de
datos.
La pérdida de datos debido a un error
del usuario puede ser localizada o generalizada. Un ejemplo de daño localizado
está eliminando a la persona equivocada en la tabla empleados. Este tipo de
lesiones requiere la detección y la reparación quirúrgica. Un ejemplo de un
daño generalizado es un trabajo por lotes que borra las órdenes de la empresa
para el mes en curso. En este caso, se requiere una acción drástica para evitar
una extensa base de datos de tiempo de inactividad.
Mientras que la formación de usuarios
y el manejo cuidadoso de los privilegios pueden prevenir la mayoría de los
errores de usuario, su estrategia de copia de seguridad determina la gracia de
recuperar los datos perdidos cuando un error del usuario que hace perder los
datos.
Errores de Aplicación
A veces, un mal funcionamiento de
software puede dañar los bloques de datos. En una corrupción física, que
también se conoce como la corrupción los medios de comunicación, la base de
datos no reconoce el bloque en absoluto: la suma de comprobación no es válida,
el bloque contiene todos los ceros, o el encabezado y el pie de página del
bloque no coinciden. Si el daño no es muy amplio, puede a menudo repara
fácilmente con bloque de recuperación de medios.
Preservación de Datos
Conservación de datos se relaciona con
la protección de datos, pero tiene un propósito diferente. Por ejemplo, puede
que tenga que conservar una copia de una base de datos tal como existía al
final de la cuarta parte del negocio. Esta copia de seguridad no es parte de la
estrategia de recuperación de desastres. Los medios a los que estas copias de
seguridad se escriben a menudo disponible después de la copia de seguridad.
Usted puede enviar la cinta en almacenamiento incendio o enviar un disco duro
portátil a un centro de pruebas. RMAN proporciona una manera conveniente para
crear una copia de seguridad y eximirla de su política de retención de copia de
seguridad. Este tipo de copia de seguridad se conoce como una copia de
seguridad de archivo.
Transferencia de
Datos
En algunas situaciones, es posible que
tenga que tomar una copia de seguridad de una base de datos o base de datos de
componentes y moverlo a otra ubicación. Por ejemplo, puede utilizar el
Administrador de recuperación (RMAN) para crear una copia de base de datos,
cree una copia de tabla que se puede importar en otra base de datos, o mover
una base de datos completa de una plataforma a otra. Estas tareas no son,
estrictamente hablando, parte de una estrategia de backup y recuperación, pero
requieren el uso de copias de seguridad de bases de datos, por lo que pueden
incluirse en las tareas de un administrador de copia de seguridad.
4.3 Comandos de
Activación para los Modos de Operación
Para ser uso de los diferentes
comandos para un modo de operación debemos estar como administrador o asuma un
rol que incluya el perfil de derechos Service Management.
Comando STARTUP
Para el arranque de una base de datos
hay tres fases de arranque, para realizar estas fases podemos utilizar startup
más un comando, las tres fases son las siguientes:
Fase de no Montaje: se
leen los parámetros del sistema, se inician las estructuras de memoria y los
procesos de segundo plano. La instancia se arranca sin asociarla a la base de
datos. Normalmente se utiliza cuando se modifica o se necesita crear el archivo
de control:
startup
nomount ;
Fase de Montaje: se
asocia la instancia con la base de datos. Se usa el archivo de parámetros para
localizar los archivos de control, que contienen el nombre de los archivos de
datos y los registros rehacer. Los archivos de datos y los registros de rehacer
no están abiertos, así que no son accesibles por usuarios finales para tareas
normales. Para realizar esta fase se pueden utilizar dos comandos:
startup
mount;
alter
database mount;
Fase de Apertura: se
abren los archivos de datos y los registros rehacer. La base de datos queda
disponible para las operaciones normales. Es necesario que existan registros
rehacer de lo contrario si no hay registros usamos el comando resetlogs, que
crea registros nuevos. Para esta fase se pueden usar dos comandos:
startup
open;
alter
database open;
Si es necesario utilizar resetlogs:
startup
open resetlogs;
alter
database open resetlogs;
startup
restrict (sólo permite la conexión de usuarios con el privilegio restricted
sesion).
startup
force (hace shutdown abort y arranca la BD).
Comando SHUTDOWN
El comando SHUTDOWN lo utilizamos parar una base de datos la cual consiste en
varias cláusulas.
Shutdown Normal: Este
es el valor por defecto, durante el proceso de parada no admite nuevas
conexiones y espera que las conexiones actuales finalicen. En el próximo
arranque la base datos no requiere procedimientos de recuperación.
Shutdown Immediate: Se
produce una parada inmediata de la base de datos, durante el proceso de parada
no permite nuevas conexiones y las actuales la desconecta, las transacciones
que no estén commit se hara roolback de ellas. En el próximo arranque la base
datos no requiere procedimientos de recuperación.
Shutdown
Transactional: Se produce una parada hasta que hayan
terminado las transacciones activas, no admite nuevas conexiones y tampoco
nuevas transacciones, una vez que las transacciones activas van terminando va
desconectando a los usuarios. En el próximo arranque la base datos no requiere
procedimientos de recuperación.
Shutdown Abort: Aborta
todos los procesos de una base de datos, durante el proceso de parada no
permite nuevas conexiones y las actuales la desconecta, las transacciones que
no estén commit se hará roolback de ellas. En el próximo arranque la base datos
puede requerir procedimientos de recuperación.
Comando Describe
Este comando permite conocer la
estructura de una tabla, las columnas que la forman y su tipo y
restricciones.
DESCRIBE f1;
Comando SHOW TABLES y
SHOW CREATE TABLE
El comando SHOW TABLES muestra las
tablas dentro de una base de datos y SHOW CREATE TABLES muestra la estructura
de creación de la tabla.
Modificación
Para realizar una modificación
utilizamos el comando ALTER TABLE. Para usar ALTER TABLE, necesita permisos
ALTER, INSERT y CREATE para la tabla.
4.4.- Manejo de
Índices
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.
4.4.1 Tipos de
Índices
Resumen
de Í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. Por ejemplo, sin un índice, una consulta de ubicación
2700 en la tabla hr.departments requiere la base de datos para buscar todas las
filas de cada bloque de la tabla para este valor. Este enfoque no escala bien
como datos de aumento de volúmenes.
Por analogía, supongamos que un
gerente de Recursos Humanos tiene un estante de cajas de cartón. Las carpetas
que contienen información de los empleados se insertan aleatoriamente en las
cajas. La carpeta de empleado Whalen (ID 200) es de 10 carpetas desde el fondo
de la caja 1, mientras que la carpeta para el rey (ID 100) se encuentra en la
parte inferior del cuadro 3. Para localizar una carpeta, el gestor busca en
cada carpeta en la casilla 1 de abajo hacia arriba, y luego se mueve de una
casilla a otra hasta que se encuentra la carpeta. Para acelerar el acceso, el
administrador puede crear un índice que enumera de forma secuencial todos los
ID de empleado con su ubicación de la carpeta:
ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8
ID 200: Box 1, position 10
Del mismo modo, el administrador
podría crear índices separados para los últimos nombres de los empleados, los
ID de departamento, y así sucesivamente.
En general, considerar la creación de
un índice en una columna en cualquiera de las siguientes situaciones:
·
Las
columnas indizadas se consultan con frecuencia y devuelven un pequeño
porcentaje del número total de filas en la tabla.
·
Existe
una restricción de integridad referencial en la columna o columnas indexadas.
El índice es un medio para evitar un bloqueo de tabla completa que de otro modo
se requeriría si se actualiza la clave principal de la tabla principal, se
funden en la tabla principal, o eliminar de la tabla primaria.
·
Una
restricción de clave única se coloca sobre la mesa y desea especificar
manualmente el índice de todas las opciones sobre índices y.
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.
Nota:
Si se le cae un índice, las aplicaciones siguen funcionando. Sin embargo, el
acceso de los datos previamente indexado puede ser más lento.
La ausencia o presencia de un índice
no requiere un cambio en el texto de cualquier sentencia SQL. Un índice es una
ruta de acceso rápido a una sola fila de datos. Sólo afecta a la velocidad de
ejecución. Dado un valor de datos que se ha indexado, el índice apunta directamente
a la ubicación de las filas que contienen ese valor.
La base de datos mantiene
automáticamente y utiliza los índices después de su creación. La base de datos
también refleja automáticamente los cambios en los datos, como agregar,
actualizar y eliminar filas, en todos los índices pertinentes sin acciones
adicionales requeridas por los usuarios. Rendimiento de recuperación de datos
indexados permanece casi constante, incluso cuando se insertan filas. Sin
embargo, la presencia de muchos índices en una tabla degrada el rendimiento DML
porque la base de datos también debe actualizar los índices.
Los índices tienen las siguientes
propiedades:
·
Facilidad de Uso
Los índices son utilizables (por
defecto) o inutilizable. Un índice inutilizables 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.
Guía del
Administrador para Aprender a Manejar los Índices
·
Base
de datos Oracle Performance Tuning Guide para aprender cómo ajustar los índices
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.
Nota:
En algunos casos, tales como cuando la columna principal tiene muy baja
cardinalidad, la base de datos puede utilizar una búsqueda selectiva de este
índice.
Múltiples índices pueden existir para
la misma mesa, siempre y cuando la permutación de columnas difiere para cada
índice. Puede crear varios índices que utilizan las mismas columnas si se
especifica claramente diferentes permutaciones de las columnas. Por ejemplo,
las siguientes sentencias SQL especifican permutaciones válidas:
CREATE
INDEX employee_idx1 ON employees (last_name, job_id);
CREATE
INDEX employee_idx2 ON employees (job_id, last_name);
Í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 únicas 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).
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 es
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.
4.4.2 Reorganización
de Índices.
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:
Id
Índice
El
número de índice dentro de la tabla.
Tabla
Nombre
de la tabla a la que corresponde el índice.
Índice
Nombre
del índice.
%
Fragmentación
El
porcentaje de fragmentación lógica (páginas del índice fuera de orden).
Cantidad
de fragmentos
La
cantidad de fragmentos (páginas físicas consecutivas) en el índice.
Promedio
de páginas por fragmentos
Promedio
de número de páginas en un fragment del índice.
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.
|
% Fragmentación
|
Sentencia
correctiva
|
|
> 5% and <
= 30%
|
ALTER INDEX
REORGANIZE
|
|
> 30%
|
ALTER INDEX REBUILD WITH (ONLINE = ON)*
|
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.
4.4.3 Reconstrucción
de Índices
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 lo siguiente:
ALTER INDEX <index_name> REBUILD
PARTITION <nb_partition> NOLOGGING;
Nota: En algunos casos cuando alguno
de los índices tiene algún tipo de corrupción no es posible reconstruirlo. La
solución en este caso es borrar el índice y recrearlo.
No hay comentarios.:
Publicar un comentario