DISEÑO FÍSICO
• Especificación de estructuras de almacenamiento internas y caminos
de acceso específicos para que las diversas aplicaciones que accedan
a la BD tengan un buen rendimiento
• Cada SGBD ofrece varias opciones:
– Diferentes tipos de ÍNDICES
– Agrupamiento de registros (de distinto tipo) relacionados en los mismos bloques de
disco (CLUSTER de ficheros)
– Distintos tipos de técnicas de dispersión (HASHING)
– Diferentes valores para los parámetros físicos (tamaño de bloque, de buffers, ...)
– ...
• El diseño físico es muy dependiente del SGBD comercial seleccionado
• Una vez elegido el SGBD, el Diseño Físico consiste en la elección e
implementación de las estructuras más apropiadas para los archivos de la BD,
entre las opciones que ofrece el SGBD
• Diseñar e implementar los mecanismos de seguridad : vistas de usuario y
reglas de acceso (privilegios/roles)
D.Físico - 1
D.FÍSICO: OBJETIVOS
CRITERIOS PARA ELEGIR OPCIONES DE DISEÑO FÍSICO (~OBJETIVOS)
• TIEMPO DE RESPUESTA (debe minimizarse)
– Tiempo entre la introducción de una transacción de BD y la obtención de respuesta
– Depende de...
• TIEMPO DE ACCESO A LA BASE DE DATOS (bajo el control del SGBD) para obtener los
datos que T necesita
• CARGA DEL SISTEMA, PLANIFICACIÓN DE TAREAS DEL SO, RETRASOS DE
COMUNICACIÓN (fuera del control del SGBD)
• APROVECHAMIENTO DEL ESPACIO (debe optimizarse)
– Cantidad de espacio ocupado por archivos de la BD y sus estructuras de acceso
• PRODUCTIVIDAD DE LAS TRANSACCIONES (debe maximizarse)
– Número promedio de transacciones que el SBD puede procesar por minuto
– Parámetro crítico de los sistemas de procesamiento masivo de transacciones
– Debe medirse en “condiciones pico” del sistema
D.Físico - 2
D.FÍSICO: OBJETIVOS
• Especificar LÍMITES PROMEDIO y del PEOR DE LOS CASOS de cada parámetro
como parte de los REQUERIMIENTOS de RENDIMIENTO del Sistema.
• Utilizar técnicas analíticas o experimentales (prototipos, simulación)
para ESTIMAR valores promedio y del peor de los casos
suponiendo diferentes decisiones de diseño físico,
para ver si se satisfacen los requerimientos de rendimiento especificados
• El rendimiento depende del TAMAÑO y NÚMERO de REGISTROS en los ficheros
 * estimar estos parámetros para cada fichero
* estimar también “cómo y cuánto va a crecer”
(en tamaño de registro, o en número de registros)
• Estimar PATRONES DE ACTUALIZACIÓN y OBTENCIÓN de datos para cada
fichero, considerando TODAS las TRANSACCIONES
D.Físico - 3
D.FÍSICO: OBJETIVOS
DISEÑO FÍSICO
inicial
ESTRUCTURAS DE
ALMACENAMIENTO
Y CAMINOS DE
ACCESO
IMPLEMENTACIÓN
BD
D.Físico - 4
Diseño FÍSICO
• Muchos sistemas incluyen UTILERÍAS DE SUPERVISIÓN que obtiene
ESTADÍSTICAS DE RENDIMIENTO
–
Nº de INVOCACIONES de TRANSACCIONES y/o CONSULTAS PREDEFINIDAS,
Actividades de Entrada/Salida para cada fichero, Nº de bloques (páginas) por
fichero, Nº de entradas por índice, Frecuencias de UTILIZACIÓN de índices, ...
• Cambios en Requerimientos del Sistema de BD (Nuevas Consultas o
Transacciones, ...)  Reorganizar la BD:
– Creación de nuevos índices, o Modificación de métodos de acceso, ...
• optimizador: decisión final sobre el camino de acceso. Pero es el diseñador
el que puede especificar los mecanismos de acceso que dispondrá el
optimizador.
– Para asegurar que el optimizador dispone de eficientes métodos de acceso:
a. Conocer los mecanismos de acceso soportados por el SGBD.
b. Evaluar las circunstancias bajo las que el DBMS utiliza el mecanismo de acceso.
c. Concentrarse sobre las solicitudes de procesamiento más críticas.
D.Físico - 5
FACTORES A CONSIDERAR EN EL D.FÍSICO
Factores que afectan al rendimiento de las aplicaciones
(transacciones y consultas)
• Objetivo del DISEÑO FÍSICO
ESTRUCTURACIÓN ADECUADA de datos en el ALMACENAMIENTO de tal forma que
GARANTICE un BUEN RENDIMIENTO de las aplicaciones
• Pero para un Esquema Conceptual podemos tener diversos Esquemas Físicos
posibles en un mismo SGBD: ¿Cuál es el más apropiado? ¿en base a qué
podemos decidirnos por uno u otro?
• Imposible analizar el RENDIMIENTO ni TOMAR DECISIONES DE DISEÑO sin
saber QUÉ USO SE LE VA A DAR A LA BASE DE DATOS
–
–
–
–
Consultas/Transacciones
Frecuencia (esperada) de Consultas y Transacciones
Restricciones de Tiempo (especiales) de Consultas y Transacciones
Frecuencia (esperada) de operaciones de Actualización de la BD
D.Físico - 6
FACTORES A CONSIDERAR EN EL D.FÍSICO
1. ANÁLISIS DE CONSULTAS Y TRANSACCIONES
Definir (alto nivel) transacciones y consultas que se espera ejecutar en la BD
• PARA CADA CONSULTA...
– TABLAS (FICHEROS) a los que accede
– CAMPOS sobre los que se especifica alguna CONDICIÓN DE SELECCIÓN 
– CAMPOS sobre los que se especifica alguna CONDICIÓN DE REUNIÓN o de ENLACE
de REGISTROS de diferente tipo 
– CAMPOS cuyos valores obtiene la consulta
• PARA CADA TRANSACCIÓN y operación de ACTUALIZACIÓN...
– TABLAS (FICHEROS) que actualiza
– Operación que realiza en cada fichero (INSERCIÓN, MODIFICACIÓN, ELIMINACIÓN)
– CAMPOS sobre los que se especifica alguna CONDICIÓN DE SELECCIÓN para las
modificaciones y borrados 
– CAMPOS actualizados (por una operación de modificación) 
() Candidatos para definir ESTRUCTURAS DE ACCESO sobre ellos
() Candidatos para EVITAR definir ESTRUCTURAS DE ACCESO sobre ellos
D.Físico - 7
FACTORES A CONSIDERAR EN EL D.FÍSICO
2. ANÁLISIS DE FRECUENCIA ESPERADA DE INVOCACIÓN DE
CONSULTAS Y TRANSACCIONES (tasas o velocidades de invocación)
Datos de
FRECUENCIAS de cada
CONSULTA y TRANSACCIÓN
Información sobre los CAMPOS
(de selección y reunión) en cada
CONSULTA y TRANSACCIÓN
FRECUENCIA ESPERADA DE USO DE CADA
CAMPO (de selección y reunión)
--considerando TODAS las CONSULTAS y
TRANSACCIONES--
Si el volumen de procesamiento es elevado, aplicar la regla informal del 80-20
 NO es necesario SUPERVISAR TODAS las Consultas y Transacciones para
recoger Estadísticas y Tasas de Invocación, sino que BASTA con hacerlo con un
20% de ellas (las que realizan el 80% del procesamiento)
D.Físico - 8
FACTORES A CONSIDERAR EN EL D.FÍSICO
3. ANÁLISIS DE RESTRICCIONES DE TIEMPO SOBRE CONSULTAS Y
TRANSACCIONES
“T debe TERMINAR ANTES de 6 segundos en el 95% de las veces que sea
invocada y NUNCA debe DURAR más de 25 segundos”
• Restricciones de Tiempo  ASIGNACIÓN DE PRIORIDADES ADICIONALES a
los campos candidatos para ESTRUCTURAS DE ACCESO
Un campo de selección utilizado por T será candidato con mayor prioridad
para estructura de acceso que otros
4. ANÁLISIS DE FRECUENCIA ESPERADA DE OPERACIONES DE
ACTUALIZACIÓN
• Los ficheros que se modifican mucho deben tener el MÍNIMO posible de
ESTRUCTURAS DE ACCESO
Actualizar el fichero (inserciones, eliminaciones, modificaciones)
 Actualizar los caminos de acceso
 más lentas las operaciones de actualización
D.Físico - 9
PROCESO DE DISEÑO FÍSICO
ANÁLISIS DE TRANSACCIONES
Y CONSULTAS SOBRE LOS DATOS
DECISIONES DE
DISEÑO FÍSICO
ESTRUCTURAS DE
ALMACENAMIENTO
Y CAMINOS DE
ACCESO
IMPLEMENTACIÓN
ESTUDIO DE ALTERNATIVAS
OFRECIDAS POR EL SGBD
PAUTAS DE DISEÑO FÍSICO
AFINAMIENTO (TUNING)
* BAJO RENDIMIENTO
NUEVAS CONSULTAS Y
TRANSACCIONES
BD
* NO CUMPLIMIENTO DE
REQUERIMIENTOS DEL
SISTEMA
SUPERVISIÓN DEL
RENDIMIENTO
D.Físico - 10
PAUTAS DE DISEÑO FÍSICO DE BD’s RELACIONALES
 ELECCIÓN DE ESTRUCTURAS DE ALMACENAMIENTO Y CAMINOS
DE ACCESO
• La mayoría de los SGBD representan cada RELACIÓN BASE como un FICHERO
• Es necesario especificar... Tipo de fichero,Tipos de estructuras de acceso
–
–
–
–
Técnicas de
Técnicas de
Técnicas de
Técnicas de
exploración (scan)
índices (index)
dispersión (hash)
agrupamiento(cluster)
 TÉCNICAS PARA ACELERAR la operación de EQUIRREUNIÓN y/o
de REUNIÓN NATURAL
– Técnicas de Agrupamiento de 2 o más tablas (Clusters)
– Desnormalización, por razones de eficiencia
D.Físico - 11
EXPLORACIÓN
• Mecanismo de acceso por defecto (tabla desordenada).
• Ineficiente si:
 Se inspeccionan muchas tuplas y se selecciona un pequeño porcentaje.
• Eficiente:
 Sobre tablas pequeñas e inspeccionadas mediante pocas E/S (factor de bloqueo).
 Alto porcentaje de seleccionadas  20%.
 Otros mecanismos de acceso son demasiado costosos.
• Como incrementar el porcentaje de tuplas recuperadas: limitando el
alcance del examen a un conjunto de tuplas que contengan todas las especificadas
(selección) y pocas no seleccionadas (rango de valores).
 Tabla de agrupación (clustered table): tuplas almacenadas en la secuencia de
valores para un conjunto específico de columnas (Tabla ordenada). Supone
mecanismo de acceso complementario que proporcione acceso directo a la primera
tupla que satisfaga la condición de búsqueda (p.e. índice o hashing).
 Particionamiento horizontal de acuerdo a algún criterio de selección.
Afinamiento (tuning):
 Utilizar dispositivos de alta velocidad para tablas frecuentemente examinadas.
 Especificar número y tamaño apropiado de buffers.
D.Físico - 12
AGRUPAMIENTO (Cluster)
• Almacenamiento de tuplas en una secuencia predeterminada basada en
los valores de una o más columnas (cluster key)
Agrupar: tablas medio-grandes ( 6 bloques físicos) que son frecuentemente
(a) ordenadas sobre esa secuencia,
(b) ó accedidas sobre criterios de selección que involucran un rango de valores
sobre una columna o conjuntos de columnas,
(c) ó procesadas de forma secuencial en esa secuencia,
(d) y que no son frecuentemente actualizadas (d1) mediante inserciones o
borrados de tuplas (d2) ó modificaciones de los valores que determinan el
agrupamiento (cluster).
¿Sobre qué columnas?:
 Que participen en ORDER BY, GROUP BY, UNION, DISTINCT y otras
operaciones que impliquen ordenación.
 Columnas de reunión (JOINS de clave primaria + clave ajena).
 Selección sobre rango de valores.
D.Físico - 13
ÍNDICES
• Son el mecanismo de acceso más dinámico: se pueden añadir, suprimir,
redefinir índices de forma más fácil que cambiar una secuencia de cluster,
redefinir una clave de dispersión o cambiar cualquier otro parámetro que
afecte al almacenamiento físico de la BD.
 se puede posponer la especificación de índices para el final del proceso de
diseño e incluso después de la implementación inicial de la BD.
• Estructura común a muchos SGBD: B-trees  soporta bien diferentes tipos de
consultas (por rango, valores extremos MIN-MAX, ...), inserciones frecuentes,
...
• Pueden ser usados para:
 Evitar el examen de tabla completa (Full table scan) (se utiliza el índice para
realizar el examen)  para ser útil: suma de E/S a índice + E/S a tabla < E/S a
tabla.
 Limitar el alcance del examen a tabla: cluster index, ordenación.
 Evitar una ordenación.
 Evitar el acceso a las tablas para determinadas consultas.
D.Físico - 14
ÍNDICES
Conviene UTILIZAR índices...
• sobre tablas medianas/grandes (ocupan gran cantidad de espacio)
• para facilitar el acceso a pequeños porcentajes del total de tuplas (≤20%)
seleccionadas
• y para evitar...
– el recorrido de la tabla completa
– el acceso a tablas para consultas que incluyen un pequeño subconjunto de
columnas (gracias a los índices compuestos)
PERSONA(ssnum, apellido, nombre, edad, telef)
CREATE INDEX idx_perso ON PERSONA(apellido, nombre)
– la ordenación de las tuplas de la tabla (ORDER BY, GROUP BY, UNION,
DISTINCT, JOIN, ...)
– el acceso a ficheros para determinadas consultas (EXISTS, IN, ...)
• Crearlos sobre columnas ...
– que suelen aparecer en cláusulas WHERE, order by, ... (ordenación)
– que suelen ser atributos de reunión de varias tablas (p.ej. claves ajenas)
– con gran variedad de valores (mayor discriminación en las búsquedas)
D.Físico - 15
ÍNDICES
Conviene EVITAR los índices...
• si la tabla es muy pequeña (ocupa poco espacio)
• si se degradan los requerimientos de procesamiento crítico
• si el costo de su almacenamiento +mantenimiento >>>> beneficio
– INSERCIÓN, MODIFICACIÓN, ELIMINACIÓN sobre tablas indexadas
 mantenimiento del índice (automático, por parte del SGBD)
– A veces conviene CREAR la tabla, rellenarla, y luego CREAR los ÍNDICES
– Y a veces conviene (actualizaciones masivas, reorganizaciones, copias de
seguridad, estadísticas): ELIMINAR ÍNDICES, realizar las MODIFICACIONES
sobre la BD y CREAR de nuevo los ÍNDICES
• Máximo de
 4 índices por tabla. Más si rara vez es actualizada
–  INDICES   necesidad de espacio +  velocidad de inserción/eliminación
• Evitar crear índices sobre columnas
– que son actualizadas muy a menudo
– con distribución irregular de valores (selectividad: confunde al optimizador)
– columnas que sólo aparezcan en cláusulas WHERE con funciones u operadores
(distintos a MIN o MAX)  pueden no hacer disponible el camino de acceso
D.Físico - 16
ÍNDICES
El SGBD Oracle ...
• Implementa los índices mediante árboles B*
• No incluye entrada en el índice para tupla con NULL en la columna de
indexación
• El Optimizador NO usa el índice construido sobre una columna, si la
consulta...
– no incluye WHERE,
– usa una columna indexada aplicandole una función (SUBSTR, ...) u
operador (||, ...)
pero el SGBD sí usa el índice si la consulta...
– contiene un ORDER BY a la columna indexada,
– aplica MAX, MIN sobre una única columna (la indexada)
D.Físico - 17
Técnicas de dispersión (Hashing)
•
•
Las tuplas son físicamente almacenadas y recuperadas de acuerdo con los resultados de una
función de dispersión (hash function): genera a distribución de valores numéricos (hash values),
basada en valores específicos de la clave de dispersión (hash key values).
Alternativa a tablas indexada / agrupada: las tuplas son localizadas usando los valores de clave
que están almacenados en un índice separado  mínimo de 2 operaciones de E/S. Una función
de dispersión no necesita E/S  mínimo una E/S para leer o escribir una tupla en la tabla.
• No todos los SGBD soportan hashing (no universal)
 Extremadamente eficiente para acceso directo.
 Solo se puede definir un hashing por tabla (determina la posición física).
Utilizar hashing para: tablas medianas – grandes.
 Frecuentes accesos individuales aleatorios.
 Especificar valores discretos (selección por igualdad) de la misma columna o
conjunto de columnas (hash key values).
 Actualizaciones infrecuentes de la hash key.
 Tamaño de la tabla conocido y no se esperan crecimientos o reducciones
significativos  dispersión estática / dispersión dinámica en caso contrario.
D.Físico - 18
Técnicas de dispersión (Hashing)
No utilizarlo:
 Accesos por rango de valores, a menos que sea una lista discreta de valores
de clave (IN (val, val, val)).
 Accesos que requieran ordenación (como acceso en secuencia lógica).
 Si se accede a la tabla sobre criterios de selección que no involucran la clave
o que involucran a parte de la misma
 Si se accede mediante búsqueda de patrón (pattern match, LIKE).
Sobre que columnas:
a) Que permitan lograr una buena distribución de tuplas.
b) Eviten colisiones (sinónimos, valores duplicados de hash: originan
encadenamientos, overflow, ...)  no se debe definir sobre columnas con
pocos valores diferentes.
c) Sobre columnas frecuentemente utilizadas para recuperar mediante
condiciones de igualdad sobre valores discretos.
d) Evirtarlo sobre columnas frecuentemente actualizadas.
D.Físico - 19
 Acelerar
la operación de equirreunión y/o de
reunión natural: Técnicas de agrupamiento
• Almacenar DOS RELACIONES (intererrelación 1:N clave primaria / clave
ajena) en un ÚNICO FICHERO según la clave del cluster: Cada registro del
lado 1 (clave primaria) está SEGUIDO de los registros del lado N (ajena)
 Consultas MUY EFICIENTES sobre la clave del cluster ...
– REUNIONES entre ambas tablas
– Agrupamiento de registros del lado N por el valor de la clave externa
(los registros relacionados están en el mismo bloque  menos accesos a bloque)
 Mayor aprovechamiento del espacio
 Consultas a cada tabla por separado resultan ineficientes (acceso multi-tabla)
 Examen completo (scan) de cada tabla por separado, más costoso
 INSERCIÓN poco eficiente debido a que se debe mantener la ordenación física, al
tiempo que desaprovechar el mínimo espacio de almacenamiento
 muchos encadenamientos entre registros (zonas de desborde, etc.)  menor
eficiencia de las búsquedas según la clave del cluster  reducción del rendimiento
de las consultas
D.Físico - 20
RESUMEN DE PAUTAS DE D.FÍSICO
(1) (Elmasri/Navathe)
Usar “FICHERO ORDENADO” y escoger como atributo de ordenación el que se
use mucho
– para obtener o procesar los registros en orden, o
– en operaciones de reunión con este fichero, o
– en selecciones que incluyen un rango de valores sobre ese atributo
CREANDO un ÍNDICE sobre ese atributo (camino de acceso primario)
• Índice PRIMARIO (atributo clave)
-- CREATE UNIQUE INDEX... CLUSTER
• Índice de AGRUPAMIENTO (no clave)
-- CREATE INDEX ... CLUSTER
• Usar un “FICHERO NO ORDENADO” si
– si ningún atributo satisface estos criterios, o
– el fichero es frecuentemente actualizado ...
• inserciones/borrados de tuplas (registros)
• modificaciones del atributo de ordenación
D.Físico - 21
RESUMEN DE PAUTAS DE DISEÑO FÍSICO
(y 2)
• Para cada atributo (no de ordenación) que se use mucho en operaciones de
selección o reunión, crear un índice SECUNDARIO -- CREATE [UNIQUE] INDEX ...
• Si el fichero se va a actualizar mucho (INSERCIÓN/ELIMINACIÓN),
reducir al MÍNIMO el número de ÍNDICES del fichero
• Usar un FICHERO DISPERSO y si algún atributo ...
– se usa mucho para selección por igualdad u operaciones de reunión
– nunca se utiliza para obtener los registros en orden
– sus valores son muchos y diferentes, y apenas se modifica su valor
y se puede CREAR ÍNDICES SECUNDARIOS sobre otros atributos del fichero
disperso
D.Físico - 22
Acelerar la operación de equirreunión y/o de reunión
natural: DESNORMALIZACIÓN
«Desnormalizar, es decir, violar la normalización, sólo tiene una excusa:
rendimiento ... y sólo en algunas situaciones » [Shasha-92]
• La normalización hasta la 3FN, FNBC o superior, facilita la comprensión de...
– los datos y
– relaciones entre los datos, que deben protegerse y mantenerse al crear las
aplicaciones
• En aplicaciones importantes o muy sencillas,
cuyas tareas no se adaptan a tablas normalizadas,
una vez terminado el análisis, puede ser necesario desnormalizar algunas tablas,
para conseguir una aplicación que se adapte a...
• las tareas de los usuarios
• los requerimientos de tiempo
D.Físico - 23
DESNORMALIZACIÓN REAL
• DESNORMALIZACIÓN REAL
– Consultas o Transacciones frecuentes que necesitan reunión de
• demasiadas tablas (tres o más), o
• dos tablas con muchas tuplas
se ejecutarán muy lentamente
– Solución: Romper la 3FN (FNBC o superior)
• Almacenar atributos de una tabla en otra tabla
• REPETICIÓN, DUPLICACIÓN o REDUNDANCIA de atributos
– Importante: las transacciones que actualizan el atributo deben MANTENER LA
CONSISTENCIA entre los duplicados (evitar anomalías)
• Procedimientos almacenados, Disparadores (Triggers), Afirmaciones
• Redundancia controlada
• DESNORMALIZACIÓN EXTREMA
Almacenar un FICHERO con la REUNIÓN de las tablas
 Resolver explícitamente las ANOMALÍAS DE ACTUALIZACIÓN
D.Físico - 24
DESNORMALIZACIÓN REAL
Esquema de base de datos no normalizado
El análisis de requerimientos indica que siempre que se necesita el perfil de un
trabajador, se requiere el nombre del responsable de donde esté alojado el
trabajador.
EMPLEADO(nombre, edad, alojamiento, responsable)
OFICIO_EMPLEADO(nombre, oficio, calificacion)
OFICIO(oficio, descripcion)
ALOJAMIENTO(alojamiento, nombre-completo, direccion, responsable)
Este esquema...
• INSERCIÓN NO DIRECTA en la tabla EMPLEADO: al insertar un empleado  el
nombre del responsable debe venir desde la tabla ALOJAMIENTO, según el valor
de “alojamiento” para el nuevo empleado
• MODIFICACIÓN del nombre de un responsable en la tabla ALOJAMIENTO, o
cambio de responsable para un alojamiento  modificación de tuplas EMPLEADO
D.Físico - 25
Estructura lógica de una BD Oracle
D.Físico - 26
Estructuras lógicas de almacenamiento
•
•
•
•
Segmento datos
Segmento de índices
Segmento de rollback
Segmento temporal
–
–
–
–
–
–
–
SELECT ... ORDER BY...
CREATE INDEX.
SELECT ... GROUP BY...
SELECT ... UNION ...
SELECT DISTINCT ...
SELECT … INSERSEC ...
SELECT ... MINUS ...
D.Físico - 27
Estructura física de Oracle
• Ficheros de datos
• Ficheros redo log
• Ficheros de
control
D.Físico - 28
Entorno de memoria en Oracle
• SGA: System/Shared Global Area
– Shared pool: library (zona sql, control y
bloqueos)+dictionary (metadatos) cache
– Database Buffer Cache
– Redo Log Buffer
• PGA: Program Global Area
D.Físico - 29
Elementos de diseño físico en ORACLE
• Tablespace
CREATE TABLESPACE TS_DATOS
DATAFILE ‘/disco1/fichero1’ SIZE 100 M,
DATAFILE ‘/disco2/fichero2’ SIZE 250 M;
• Tablas y extensiones
CREATE TABLE Alumnos (…) TABLESPACE TS_DATOS
PCTFREE 20 PCTUSED
STORAGE (INITIAL 20K NEXT 30K MINEXTENTS 1
MAXEXTENTS 10 PCTINCREASE 0);
D.Físico - 30
Elementos de diseño físico en ORACLE
• Índices (dentro de create table)
…….
CREATE INDEX nom_ind ON Alumnos (atributos)
….
• Clusters
CREATE CLUSTER CL_1(clave number(4)) SIZE 512
TABLESPACE TS_DATOS PCTFREE 20 STORAGE(…..);
CREATE TABLE Alumno (….) CLUSTER CL1_(a1);
• Dispersión
CREATE CLUSTER CL_1() SIZE 512
HASH IS clave HASKEY 300
……
D.Físico - 31
Disparadores en Oracle
• Uso de disparadores
– Evitar ejecución de transacciones inválidas
– Garantizar el cumplimiento de restricciones de integridad y
de reglas de negocio
– Generar automáticamente valores de columnas derivadas
• Mal uso
– Para garantizar el cumplimiento de restricciones que puedan
ser definidas a nivel de esquema  CHECK
– Disparadores recursivos
– Gran tamaño  Procedimiento almacenado
D.Físico - 32
Creación de disparadores
CREATE TRIGGER BUpCUOTA
BEFORE UPDATE OF f_pago ON Cuota
FOR EACH ROW
WHEN (new.f_pago > old.f_venc)
BEGIN
raise_application_error(-20000, ‘Cuota ‘ ||
TO_CHAR(:old.num_cuota) || ‘ del prestamo ‘ ||
TO_CHAR(:old.num_prest) || ‘ vencida. Por favor, dirigirse a la
gerencia.’);
END;
D.Físico - 33
Sobre la creación de disparadores
•
Los nombres de los triggers deben ser únicos dentro de un esquema dado.
•
Alguna de las dos, BEFORE o AFTER, debe ser utilizada en el CREATE TRIGGER.
•
La sentencia activadora especifica el tipo de operación que despierta el disparador (DELETE,
INSERT o UPDATE). En la sentencia activadora se especifica la tabla asociada al trigger. Puede
especificarse exactamente una tabla (no una vista) en la sentencia activadora.
•
Si la sentencia activadora especifica un UPDATE se puede incluir una lista de columnas en
dicha sentencia. Si se incluye la lista de columnas, el trigger se activa por un UPDATE sólo si
una de las columnas especificadas es actualizada. Si se omite la lista, el trigger se activa cuando
cualquier columna de la tabla se actualiza. No se puede especificar lista de columnas para
INSERT o DELETE.
•
La presencia o ausencia de la opción FOR EACH ROW determina si el disparador es a nivel
de filas (row trigger) o a nivel de sentencia activadora (statement trigger). Especifica que el
cuerpo del trigger se ejecuta individualmente para cada una de las filas de la tabla que haya sido
afectada por la sentencia activadora.
•
Opcionalmente, se pueden incluir restricciones en la definición de un row trigger. Para ello se
especifica, en una cláusula WHEN, una expresión booleana de SQL. Si se incluye una cláusula
WHEN, la expresión se evalúa para cada una de las filas que el disparador afecta. Si el resultado
de la evaluación es TRUE, se ejecuta el cuerpo del trigger sobre la fila que hizo cierta la
expresión. La expresión en una cláusula WHEN no puede incluir subqueries.
D.Físico - 34
Cuerpo de un disparador
• Bloque de PL/SQL
• Disparador que se activa con Insert OR Delete
or Update
– If Inserting/Updating/Deleting THEN….. END IF;
• Update OF (atributos)
• Row trigger: new, old
• Rollback en caso de error si no hay manejo
específico de la excepción
D.Físico - 35
Modificar disparadores
• No hay modificación explícita, se reemplaza.
1)CREATE OR REPLACE TRIGGER BUpCUOTA
2) DROP TRIGGER BUpCUOTA ; CREATE TRIGGER BUpCUOTA
• (Des)habilitar
ALTER TRIGGER BUpCUOTA ENABLE/DISABLE;
ALTER TABLE CUOTA ENABLE/DISABLE ALL TRIGGERS;
D.Físico - 36
Ejemplo de trigger
CREATE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF sal, job_classification ON emp FOR EACH ROW
DECLARE
minsal
NUMBER;
maxsal
NUMBER;
salary_out_of_range EXCEPTION;
BEGIN
SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade
WHERE job_classification = :new.job_classification;
IF (:new.sal < minsal OR :new.sal > maxsal) THEN
RAISE salary_out_of_range;
END IF;
EXCEPTION
WHEN salary_out_of_range THEN
raise_application_error (-20300, 'Salary '||TO_CHAR(:new.sal)||' out of range for '
||'job classification '||:new.job_classification ||' for employee '||:new.name);
WHEN NO_DATA_FOUND THEN
raise_application_error(-20322, 'Invalid Job Classification ' ||:new.job_classification);
END;
D.Físico - 37
Ejemplo de trigger
Para generar valores de columnas derivadas:
BEFORE INSERT OR UPDATE OF ename ON emp
FOR EACH ROW
BEGIN
:new.uppername := UPPER(:new.ename);
:new.soundexname := SOUNDEX(:new.ename);
END;
D.Físico - 38
Descargar

DISEÑO FÍSICO relacional