Manual del estudiante de Ingeniería en Sistemas de UTN/Bases de datos avanzadas




Búsqueda personalizada



Manual del estudiante de Ingeniería en Sistemas de UTN/Bases de datos avanzadas/Arquitectura de la base de datos Oracle

nstancia Oracle

Cada vez que se arranca una base de datos se asigna en la memoria un Área Global del Sistema (SGA), que emplean los usuarios para compartir información de la base, y algunos procesos background de Oracle son inicializados. Estos procesos, junto con la memoria buffer, constituyen la Instancia Oracle.

Los procesos de usuario y los procesos Oracle

Un proceso de usuario ejecuta el código de un programa de aplicación o una herramienta Oracle, y se comunica con los procesos del servidor.
Los procesos del servidor son creados por Oracle para capturar los requerimientos de los procesos de usuario.
Los procesos background realizan las operaciones de I/O y monitorean a los otros procesos; lo realizan asincrónicamente para proveer mayor paralelismo y mejorar la performance.
Database Writer (DBWR)
Escribe los bloques modificados desde el DB buffer a los datafiles.
Log Writer (LGWR)
Escribe las entradas del redo log generadas en el redo log buffer al disco.
Checkpoint (CKPT)
Da una señal al DBWR de los checkpoints y actualiza todos los datafiles y control files para indicar el más reciente checkpoint.
System Monitor (SMON)
Realiza la recuperación de la instancia cuando se realiza el startup. Limpia los segmentos temporales y recupera transacciones muertas durante alguna falla. Agrupa extents libres que tienen PCTINCREASE=1.
Process Monitor (PMON)
Realiza la recuperación de los procesos cuando un proceso de usuario falla; limpia el caché y libera recursos que el proceso usaba.
Archiver (ARCH)
Copia el redo log file online a un almacenamiento de archivo cuando está lleno.
Recoverer (RECO)
Resuelve transacciones distribuidas que quedaron pendientes durante una falla en una DB distribuida.
Dispatcher (Dnnn)
Se presentan cuando es usada una configuración de server multithread.
Lock (LCKn)
Es usado para el bloqueo inter-instancia en el Oracle Parallel Server.

Estructura lógica de la DB

Tablespaces

La DB está dividida en una o más unidades lógicas de almacenamiento llamadas tablespaces, que a su vez pueden estar constituidos por uno o más archivos del S.O., llamados datafiles. Representan un nivel medio entre la DB y los datafiles. Por su parte, un datafile puede ser asociado con sólo una tablespace y una base de datos.

Data blocks

Un bloque de datos del Oracle Server es la menor unidad de almacenamiento usada por la base de datos.

Extents

Un extent es un conjunto de bloques de datos contiguos.

Segments

Un conjunto de uno o más extents que contiene todos los datos para una estructura específica en un tablespace.
  • El segmento de datos es una colección de extents que mantiene todos los datos para una tabla o cluster.
  • El segmento de índices mantiene todos los datos para un índice.
  • El segmento de rollback mantiene datos para rollback, consistencia de lecturas o recuperación
  • El segmento temporario es una colección de extents que mantiene datos pertenecientes a objetos temporales (consultas largas que necesitan guardar resultados intermedios).

Schemas Objects

Es la estructura lógica que refiere directamente a los datos de la DB.

Consideraciones

  • Especificaciones a nivel de segmento solapan las del tablespace (no MIN. EXTENT).
  • Un tamaño de extensión mínima se aplica a todas las asignadas al tablespace.
  • Por omisión se emplean las especificaciones del tablespace.
  • Cuando no se tienen especificaciones para el tablespace se emplean las del Servidor ORACLE.
  • La modificación de parámetros de almacenamiento se aplican a extensiones futuras.
  • Existen parámetros que se especifican a nivel de segmento no de tablespace.

Estructura física de la DB

Datafiles

Contienen todos los datos de la base de datos, como las tablas e índices.

Redo Log files

Mantienen registros de todos los cambios hechos a la base de datos, con fines de recuperación.

Control files

Almacenan la estructura física y el estado de la base de datos.

Bloques de datos, Extensiones y Segmentos

Estas son las unidades de asignación de espacio para una Base de Datos. Un Bloque de Datos se corresponde con un número específico de bytes relacionado con el espacio de datos físico en el disco.
Oracle requiere los datos en múltiplos del Bloque de Datos de Oracle. Cuando se crea la Base de Datos Oracle se debe setear la medida del Bloque de Datos (parámetro db_block_size), procurando que sea un múltiplo de la medida del bloque del sistema operativo, dentro de un límite máximo para evitar I/O innecesarios.
La extensión (extent) que es un número específico de Bloques de Datos contiguos asignados para almacenar un tipo específico de información.
Un segmento es un conjunto de extensiones que se han asignado a un tipo específico de estructura de datos. Por ejemplo cada tabla de datos es almacenada en su propio segmento de datos, mientras que cada índice de datos es almacenado en su propio segmento de índice.
Cuando una extensión existente en un segmento esta llena, Oracle asigna otra extensión para ese segmento.


Debido a que las extensiones son asignadas en la medida que son necesarias, las extensiones de los segmentos pueden o no ser contiguas en el disco.
Un segmento y todas sus extensiones son almacenados en un Tablespace, dentro del cual un segmento puede extenderse sobre los archivos de datos (tener extensiones con datos en más de un archivo). Cada extensión puede contener datos de un archivo solamente.

Formato del bloque de datos

El bloque está dividido en el overhead, los datos de fila, y el espacio libre entre ellos dos.

El overhead

El overhead está constituido por:
Cabecera (Header)
contiene información general del bloque tales como la dirección del bloque y el tipo de segmento (datos, índices o rollback).
Directorio de tablas (Table Directory)
contiene información acerca de aquellas tablas que tienen filas en este bloque.
Directorio de filas (Row Directory)
contiene información sobre las filas actuales en el bloque, incluyendo direcciones para cada pedazo de fila en el área de datos.

Datos de fila

Contiene los datos de tablas o índices.

Espacio libre

Es asignado para insertar filas nuevas y actualizar aquellas que requieren espacio adicional. El espacio libre debe albergar también los datos de la transacción (transaction entry), que se requiere en un bloque por cada INSERT, UPDATE, DELETE y SELECT...FOR UPDATE que acceden a una o más filas en el bloque.

PCTFREE, PCTUSED y Encadenamiento de filas

PCTFREE Y PCTUSED permiten controlar el espacio libre para inserciones y eliminaciones de filas en los bloques de un segmento.
Estos parámetros se especifican cuando se crea o altera una tabla o cluster; para el caso de los índices se puede especificar PCTFREE.
PCTFREE
Indica el porcentaje mínimo de un bloque de datos que se debe reservar como espacio libre.
PCTUSED
Indica cuando un bloque puede volver a emplearse para insertar nuevos datos de filas.
Un bloque está disponible para inserción mientras tenga libre el porcentaje que indica PCTFREE. Cuando éste es menor, se marcará como no disponible para inserciones, hasta que el porcentaje de uso caiga debajo del parámetro PCTUSED.
En dos casos, los datos de una tabla pueden ser demasiado largos para encajar en un bloque de datos:
  • La fila es demasiado larga cuando se inserta por primera vez, en cuyo caso Oracle almacena los datos para la fila en una cadena de bloques de datos (una o más) reservadas para ese segmento.
  • Una fila que originalmente encaja se actualiza de modo tal que la longitud global se incrementa y el espacio libre del bloque se encuentra lleno; en este caso Oracle mueve la fila entera a un bloque de datos nuevo.

Asignación y liberación de espacio

Cuando el espacio existente en un segmento es usado completamente, Oracle asigna un nuevo extent para el segmento.

Asignación de extents

Cuando se crea una tabla, Oracle asigna al segmento de datos de la tabla un extent inicial compuesto por un número específico de bloques de datos.
Si los bloques de datos de un extent inicial del segmento se llenan, Oracle automáticamente asigna un extent incremental para ese segmento, que es un extent subsecuente del mismo tamaño ó de un tamaño mayor que el extent asignado previamente en ese segmento. Para propósitos de mantenimiento el bloque cabecera de cada segmento contiene un directorio de los extents de ese segmento.
Algoritmo de asignación de segmentos
  • Oracle busca en el espacio libre (del tablespace que contiene los segmentos) hasta el encontrar el primer conjunto de bloques de datos contiguos, libre, de igual o mayor tamaño que el extent incremental:
    • Coincidencia exacta: se busca un conjunto que coincidan con el tamaño del extent nuevo más un bloque adicional para reducir la fragmentación interna.
    • Espacio contiguo mayor: si encuentra un grupo mayor en al menos 5 bloques que el necesitado, divide el grupo de bloques en extents separados uno de los cuales es del tamaño que se requiere.
    • Reasignación de espacio: si Oracle no encuentra un bloque de datos contiguo igual o mayor, reordena los bloques libres de su correspondiente tablespace para formar conjuntos de bloques de datos contiguos mayores. Luego aplica los pasos anteriores.
    • Autoextensión: Si un extent no puede ser asignado después de una segunda búsqueda, Oracle trata de redimensionar los archivos por autoextensión. Si no puede hacerlo retorna un error.
  • Una vez que Oracle encuentra y asigna el espacio libre necesario en el tablespace, asigna una porción del espacio libre que corresponde al tamaño del extent incremental. Si hay remanente, queda como espacio libre.
  • Oracle actualiza la cabecera del segmento y el diccionario de datos para mostrar que un extent nuevo ha sido asignado y que el espacio asignado no está más libre.

Liberación de los extents

En general, los extents de un segmento no retornan al tablespace mientras no se eliminan los objetos cuyos datos están almacenados en el segmento (por medio de un DROP TABLE o DROP CLUSTER).
Excepciones a esto:
  • Se puede truncar la tabla o cluster con la sentencia TRUNCATE...DROP STORAGE.
  • Periódicamente, Oracle puede desasignar uno o más extents de un segmento de rollback si tienen la opción OPTIMAL especificada.
  • Un DBA puede desasignar extents no utilizados usando la instrucción SQL ALTER TABLE nombre_tabla DEALLOCATE UNUSED.
Tablas Nonclustered
En tanto existan las tablas nonclustered o hasta que se las trunca, cualquier bloque de datos asignado a su segmento de datos permanece asignado a la tabla. Después de eliminarse una tabla nonclustered, este espacio puede ser reclamado cuando otros extents requieran espacio libre.
Tablas Clustered
Almacenan su información en los segmento de datos creados para el cluster. Si se elimina una tabla en un cluster, el segmento de datos permanece para las otras tablas en el cluster y ningún extent se desasigna.
Indices
Todos los extents asignados en un segmento índice permanecen asignados en tanto y en cuanto exista el índice.
] Segmentos Rollback
Oracle periódicamente chequea para ver si los segmentos de rollback de una base de datos han crecido más que su tamaño óptimo. Si un segmento rollback es mayor que su óptimo, Oracle automáticamente desasigna uno o más extents de su segmento de rollback.
Segmentos temporarios
Cuando se completa la ejecución de una sentencia que requiere un segmento temporario, Oracle automáticamente lo elimina.

Segmentos

Un segmento es un conjunto de extents que contienen todos los datos para una estructura de almacenamiento lógico específica.

Segmentos de datos

Cada tabla o partición nonclustered y cada cluster en una base de datos Oracle tiene un segmento de datos simple para mantener todos sus datos.


Los parámetros de almacenamiento determinan como sus extents de segmentos de datos son asignados. Estos parámetros afectan la eficiencia de la recuperación de datos y almacenamiento para el segmento de datos asociado con el objeto.

Segmentos de índices

Sirven para mantener los datos de los índices. Se pueden especificar los parámetros de almacenamiento para los extents del segmento de índices y el tablespace en el que se crea el segmento índice.

Segmentos temporarios

Constituyen un área de trabajo para las actividades de ordenamiento. Si dicha operación puede ser realizada en memoria este segmento no se crea. Las consultas que pueden requerir de segmentos temporarios son:
  • CREATE INDEX
  • SELECT ... ORDER BY
  • SELECT DISTINCT
  • SELECT ... GROUP BY
  • SELECT...UNION
  • SELECT...INTERSECT
  • SELECT...MINUS
  • Joins indexados o con subconsultas

Segmentos de rollback

Un segmento de rollback registra los valores viejos de los datos que fueron cambiados por cada transacción (cometida o no).

Contenido de los segmentos de rollback

Consiste de varias entradas de rollback, que incluyen información del bloque y los datos como existían antes de la operación en una transacción.


Sólo Oracle puede acceder a los segmentos de Rollback, ni los usuarios ni el DBA pueden acceder a ellos.
Las entradas de rollback cambian los bloques de datos en los segmento de rollback y Oracle registra todos los cambios de los bloques de datos, incluyendo las entradas de rollback en los redo log (que son por lo menos dos).
Si hay una falla en el sistema, Oracle automáticamente recupera la información del segmento, incluyendo las entradas de rollback para transacciones interactivas.
Una vez que se completa la recuperación, Oracle realiza los rollback de las transacciones que ni fueron completadas ni vueltas a atrás en el momento de la falla.


Para cada segmento rollback, Oracle mantiene una tabla de transacciones:

  • Una lista de todas las transacciones que usan el segmento de rollback.
  • Las entradas de rollback por cada cambio realizado por estas transacciones.
Los segmentos de rollback registran los valores de los datos antes de los cambios para cada transacción, luego vincula cada nuevo cambio al cambio previo. Si se deben recuperar una transacción, Oracle aplica los cambios en cadena a los bloques de datos en el orden que restablezcan los datos a sus valores previos.

Transacciones y segmentos rollback

Cada vez que una transacción de usuario comienza, se le asigna un segmento de rollback en una de las siguientes dos maneras:
  • Oracle puede asignar una transacción automáticamente al próximo segmento de rollback disponible. La asignación de la transacción ocurre cuando se edita la primera sentencia DML o DDL en la transacción. Nunca se asignan transacciones de solo lectura a un segmento rollback.
  • Una aplicación puede asignar una transacción a un segmento de rollback específico. Al comienzo de una transacción, un desarrollador o usuario de aplicaciones puede especificar un segmento rollback particular que Oracle debería usar cuando se ejecute la transacción.
Cuando una transacción se completa (commit), Oracle libera la información de rollback pero no la destruye inmediatamente. La información permanece en el segmento de rollback para crear vistas consistentes de lectura de los datos pertinentes para las queries que comenzaron antes que la transacción se completara exitosamente.
Oracle escribe los extents en los segmentos de rollback secuencialmente. Cuando el último extent del segmento rollback se llena, Oracle continúa escribiendo datos de rollback sobreescribiendo el primer extent en el segmento.
Una transacción que se ejecute en un período de tiempo largo puede requerir un nuevo extent para asignar un segmento de rollback.
Oracle siempre trata de reusar los extents ya asignados a una transacción.
Si el próximo extent contiene datos de transacciones que están activas todavía entonces debe asignar un extent nuevo. Esto se hace hasta que el número de extents para un segmento alcanza el parámetro de almacenamiento MAXEXTENTS.

  • PCTINCREASE le indica a Oracle cuanto ha de crecer cada extent luego que fueren usados los extents INITIAL y NEXT.
  • INITRANS, MAXTRANS especifica el número de entradas inicial y máximo que las transacciones tendrán en el bloque. Están relacionados con la concurrencia que se permite en el bloque.

  

 

Manual del estudiante de Ingeniería en Sistemas de UTN/Bases de datos avanzadas/Los archivos de datos

El DBA utiliza las tablespaces para
  • Controlar la asignación del espacio de disco para la base de datos.
  • Asignar quotas de espacio para cada usuario.
  • Controlar disponibilidad de los datos a través de tablespaces online u offline.
  • Realizar backup parcial o recuperación de base de datos.
  • Asignar almacenamiento de datos a través de dispositivos para mejorar la performance.
Un DBA puede
  • Crear y eliminar tablespaces.
  • Agregar archivos a las tablespaces.
  • Designar o alterar los parámetros por omisión para los segmentos de almacenamiento creados en la tablespace.
  • Hacer una tablespace solo-lectura o solo-escritura.
  • Hacer una tablespace temporaria o permanente.

SYSTEM Tablespace

Se crea automáticamente junto con la creación de la base de datos. Contiene las tablas del diccionario de datos para toda la base. Oracle recomienda que se cree al menos una tablespace adicional para almacenar datos de usuario, separada de la información del diccionario de datos. Esto reduce la disputa entre los objetos del diccionario y los objetos esquema para los mismos archivos. La tablespace SYSTEM está siempre online cuando se abre la base. Todos los datos almacenados correspondientes a unidades de programas PL/SQL (procedimientos, funciones, paquetes y triggers) residen en la tablespace SYSTEM.

Asignar más espacio a una base de datos

Se puede ampliar una base de tres formas:
  • Agregar un archivo a una tablespace.
ALTER TABLESPACE system ADD DATAFILE 'data2.ora'
  • Agregar una tablespace nueva.
CREATE TABLESPACE users DATAFILE 'data2.ora'
  • Incrementar el tamaño de un archivo de datos.
ALTER DATABASE DATAFILE 'data2.ora' AUTOEXTEND ON NEXT 20M MAXSIZE 1000m;
Se sugieren que cada base de datos tenga como mínimo 5 tablespaces además de SYSYEM:
  • TEMP (para los datos temporarios que se generan en las consultas)
  • RBS (rollback segments)
  • TOOLS (para los Forms, Report Writer y Designer)
  • APPL_DATA (para los applications data)
  • APPL_INDX (para los applications index)
Las consideraciones por las que se sugieren estas divisiones son:
  • Minimizar la fragmentación
  • Minimizar la competencia por el disco
  • Separar los segmentos
  • Almacenar los archivos de datos
Los segmentos más propensos a la fragmentación son, en orden:
  • TEMP
  • RBSEGS
  • INDICES
  • TABLAS
  • DICCIONARIO de DATOS
La competencia por el disco se puede minimizar separando los grupos de segmentos que pueden ser accedidos simultáneamente:
  • Los segmentos del diccionario de datos y los demás segmentos.
  • Los segmentos de rollback de los demás segmentos.
  • Los segmentos de datos de sus correspondientes segmentos de índices.
Separar las tablas de acuerdo con sus características (necesidades de backup, requerimientos de acceso, necesidades de uso, crecimiento) deviene en ventajas en:
  • Recuperación y backup
  • Tamaño
  • Seguridad
  • Limpieza y claridad
Recomendaciones de Administración de los Archivos de Bases de Datos
  • Mantener al menos dos copias activas de los archivos de control[1] de la base de datos en al menos dos dispositivos físicos diferentes.
  • Mantener múltiples archivos de redo log y ubicarlos por grupos en diferentes discos.
  • Separar los tablespaces que puedan competir por recursos en discos y ubicarlos en diferentes discos físicos.

Tablespaces Online y Offline

Un DBA puede poner cualquier tablespace (excepto la SYSTEM) de una base de datos Oracle online (accesible) u offline (inaccesible) siempre y cuando la base de datos esté abierta.
Colocar una tablespace offline puede ser necesario para:
  • Hacer una porción de la base de datos inaccesible mientras permite acceso normalmente al resto.
  • Realizar un backup de la tablespace offline.
  • Hacer que una aplicación y su grupo de tablas temporalmente inaccesible mientras duren las tareas de actualización y mantenimiento.
La tablespace SYSTEM está necesariamente siempre online, porque allí reside el diccionario de datos, que Oracle necesita para funcionar.
No se puede llevar a una tablespace offline si contiene algún segmento de rollback que esté en uso.
Cuando una tablespace se pone offline, Oracle no permite que ninguna instrucción SQL referencie los objetos contenidos en esa tablespace.
Las transacciones activas no son afectadas por esto. Oracle guarda los datos de rollback correspondientes a aquellas sentencias en un segmento de rollback diferido (en la SYSTEM tablespace). Si es necesario cuando la tablespace se coloca online se aplican los rollbacks.
Cuando una tablespace se va a offline o vuelve a online, esto es registrado en el diccionario de datos. Así cuando se cierra una base, la tablespace permanece offline cuando la base sea reabierta o montada.
Una tablespace se puede llevar a online solo en la base en la cual fue creada, porque la información del diccionario de datos necesaria se mantiene en la SYSTEM.
Una tablespace offline no se puede leer ni editar por cualquier otra aplicación que no sea Oracle.
Las tablespaces no se pueden transferir de base a base.

Uso de las tablespaces para procedimientos especiales

Si dos tablespaces son usadas para separar datos de la tabla con los de índice pueden suceder dos cosas:
  • Si la tablespace que contiene los índices está offline, las queries pueden aún acceder los datos de la tabla porque los queries no requieren un índice para acceder las tablas.
  • Si la tablespace que contiene las tablas está offline, los datos en la base no son accesibles porque las tablas requieren acceder a los datos.

Tablespace de Solo-Lectura (Read-Only)

Su propósito es eliminar la necesidad de hacer backup y recuperación de porciones grandes y estáticas de una base. Los archivos de estos tablespaces pueden estar en un medio de solo-lectura.
Hacer una tablespace read-only no cambia su estado offline u online.
Para escribir se lo tiene que llevar a write-only con un comando ALTER TABLESPACE.
Se pueden suprimir ítems, tales como tablas o índices, desde una tablespace read-only, tal como se lo hace en una tablespace offline. Sin embargo, no se pueden ni crear ni modificar objetos. No se pueden añadir archivos, aún llevando la tablespace a offline.

Tablespaces temporarias

Se puede administrar espacio para operaciones de ordenamiento más eficientes a través del uso de tablespace temporarias exclusivas, para eliminar la serialización de la administración del espacio para operaciones de este tipo.
No pueden residir objetos permanentes en tablespace temporarias. Son útiles cuando se tienen múltiples operaciones de sort (joins, creación de índices, GRUP BY, ORDER BY, ANALYZE) que son demasiado grandes para hacerlas en la memoria.
El segmento de sort se asigna y se crea al momento de la primera operación de sort.

Archivos de datos (Datafiles)

Oracle crea un archivo para una tablespace asignando la cantidad específica de espacio de disco más el overhead requerido para la cabecera. El sistema operativo se encarga de todos las autorizaciones para crear archivos.

Contenidos de los archivos de datos

Cuando se crea un archivo, el espacio asignado es formateado pero no contiene dato alguno, sin embargo Oracle reserva el espacio para mantener los datos para segmentos futuros de la tablespace asociada.

Tamaño de los archivos de datos

El tamaño de un archivo se puede alterar después de su creación o se puede especificar que un archivo crezca dinámicamente como los objetos schema crecen en una tablespace.

Archivos Offline

Todos los archivos que conforman una tablespace son llevados offline u online como una unidad cuando se lleva a la tablespace online u offline respectivamente. Se pueden llevar los archivos offline individualmente, sin embargo esto es normalmente hecho solamente durante algunos procedimientos de recuperación.


^  The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:
  • Database information (RESETLOGS SCN and their time stamp)
  • Archive log history
  • Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
  • Redo threads (current online redo log)
  • Database's creation date
  • Database name
  • Current archive log mode
  • Log records (sequence numbers, SCN range in each log)
  • RMAN catalog
  • Database block corruption information
  • Database ID, which is unique to each DB

 

 

Manual del estudiante de Ingeniería en Sistemas de UTN/Bases de datos avanzadas/El diccionario de datos

El diccionario de datos es un conjunto de tablas solo-lectura que contienen información acerca de su base de datos asociada:
  • Nombre de los usuarios, y sus privilegios y roles.
  • Nombre de los objetos del schema.
  • Información acerca de las constraints.
  • Valores por omisión para las columnas.
  • Información de auditoría.
  • Espacio asignado y utilizado por los objetos.
Durante la operación de una base de datos, Oracle lee el diccionario de datos para averiguar qué objetos existen y cuales usuarios tienen los permisos de acceso a ellos; asimismo actualiza dicha información.
Debido a los continuos accesos al diccionario de datos, gran parte de éste está cacheado en el SGA. Toda la información se almacena en memoria usando el algoritmo LRU.
La estructura del diccionario consiste en:
  • Tablas base, que sólo Oracle debe escribir y leer, ya que están normalizadas y la mayoría de sus datos están encriptados.
  • Vistas de usuario, que decodifican y resumen la información de las tablas base convenientemente.
El propietario de estas tablas y vistas es el usuario SYS.

Contenido

[ocultar]
  • 1 Otros programas y el diccionario de datos
  • 2 Agregar nuevos ítems
  • 3 Borrar ítems de un diccionario de datos
  • 4 Las vistas del diccionario de datos
    • 4.1 Prefijo USER
    • 4.2 Prefijo ALL
    • 4.3 Prefijo DBA

Otros programas y el diccionario de datos

Otros productos Oracle pueden crear tablas de diccionario de datos adicionales o vistas propias y referenciar vistas existentes. Los desarrolladores de aplicaciones que escriben programas que referencian al diccionario deben referirse a los sinónimos públicos en vez de las tablas, ya que los sinónimos son menos propensos a los cambios entre las versiones de software.

Agregar nuevos ítems

Se pueden agregar tablas nuevas o vistas al diccionario de datos. Si se agregan objetos nuevos, el dueño de los mismos debe ser el usuario SYSTEM o un tercero. Nunca crear objetos con el usuario SYS, excepto cuando se ejecuten scripts provistos por Oracle.

Borrar ítems de un diccionario de datos

Debido a que todos los cambios al diccionario de datos son realizados por Oracle como respuesta a sentencias DDL, ningún dato en las tablas del diccionario de datos debería ser alterado o borrado por cualquier usuario. La única excepción a esta regla es la tabla SYS.AUD.

Las vistas del diccionario de datos

Prefijo USER

Se refieren al ambiente privado del usuario en la base de datos, incluyendo información acerca de los objetos creados por el usuario, y sus permisos; un subconjunto de información de las vistas ALL.
Tienen idénticas columnas a las otras vistas, excepto que la columna OWNER está implícita.
Puede tener sinónimos PUBLIC abreviados para conveniencia.
Ejemplo:
SELECT object_name, object_type FROM user_objects;

Prefijo ALL

Se refieren a la perspectiva completa del usuario en toda la base de datos. Estas vistas retornan información acerca de los objetos en los cuales estos usuarios tienen acceso público o concesiones explícitas de privilegios y roles.
Ejemplo:
SELECT owner, object_name, object_type
FROM all_objects;

Prefijo DBA

Muestran una vista global de la base de datos completa. Por lo tanto, están destinados a los administradores. Cualquier usuario que tiene garantizado el privilegio SELECT ANY TABLE también puede acceder a estas vistas.
Ejemplo:
SELECT owner, object_name, object_type
FROM sys.dba_objects
Los administradores pueden ejecutar un script denominado DBA_SYNONYMS.SQL para crear sinónimos especiales de las vistas DBA.





Manual del estudiante de Ingeniería en Sistemas de UTN/Bases de datos avanzadas/Objetos de schema

Asociada con cada BD tenemos un schema que esta compuesto por múltiples objetos:
  • Tablas
  • Vistas
  • Secuencias
  • Sinónimos
  • Índices
  • Triggers
  • Clusters
  • Vistas materializadas (snapshots)
  • Procedimientos
  • Funciones
  • Paquetes
Estos objetos constituyen estructuras de almacenamiento lógicas, no tienen una correspondencia uno a uno con los archivos en discos que los almacenan. Tampoco hay una relación entre los objetos del schema y los tablespaces: un tablespace puede tener objetos de diferentes schemas, y los objetos de un schema pueden estar contenidos en diferentes tablespaces.

Tablas

  • Las columnas usualmente se almacenan en el orden en el que fueron listadas en el CREATE TABLE (pero no se garantiza, una columna LONG se almacena último).
  • Si la tabla es alterada (ALTER) la nueva columna que se agrega se almacena último.
  • Las columnas que almacenan más frecuentemente valores nulos deberían ir al final.
  • Un null es la ausencia de un valor en una columna de una fila de la tabla. No se debe emplear para describir un valor 0.
  • Es útil, cuando se puede, emplear valores default para una columna de una tabla.

Una tabla se puede particionar:
  • Una o más particiones, c/u con filas que tienen una rango específico de valores de claves.
  • También se pueden particionar los índices.
  • Las particiones tienen las mismas restricciones definidas.
  • Cada partición puede tener atributos físicos diferentes (tablespaces diferentes).
  • Existen comandos especiales para manejar una partición (mover, coalescer, mezclar, etc.).
Ventajas de la partición:
  • Reduce la posibilidad de corrupción de datos.
  • Recuperación ante fallas y backup independientes.
  • Reduce el tiempo de recuperación después de las fallas.
  • Mejora la performance, disponibilidad y su administración.
  • Transparencia de las particiones.




Cómo se almacenan los datos de table

Cuando se crea una tabla, Oracle automáticamente asigna un segmento de datos en un tablespace para guardar los futuros datos de la tabla (excepto en tablas clustered y temporales). Se puede controlar la asignación de espacio de dos formas:
  • Controlando la cantidad de espacio asignado al segmento de datos seteando los parámetros de almacenamiento del segmento de datos.
  • Controlando el uso de espacio libre de los bloques de datos que constituyen la extensión del segmento de datos seteando el PCTFREE y el PCTUSED del segmento de datos.
El tablespace que contiene el segmento de datos de una tabla es el tablespace por omisión del propietario de la tabla, o bien el tablespace especificado en la sentencia CREATE TABLE.

Formato y tamaño de las filas

Oracle almacena cada fila como uno o más pedazos de fila, dependiendo si entra o no en un bloque. En este último caso, los pedazos de fila correpsondientes a una fila se encadenan a través de múltiples bloques, utilizando los rowids de los pedazos.
Cada pedazo de fila contiene un encabezado y datos para las columnas. Las columnas individuales pueden extenderse a lo largo de distintos pedazos y, en consecuencia, distintos bloques.
El encabezado precede a los datos y contiene información de:
  • Los pedazos de fila.
  • El encadenado.
  • Columnas del pedazo de fila.
  • Claves cluster (sólo para datos clustered).
Después del encabezado (3 bytes), cada fila contiene los largos de columna y datos. Para el largo de columna se requiere 1 byte si la columna almacena 250 bytes o menos, y de lo contrario 3 bytes. El espacio requerido por los datos depende del tipo de datos. Si es de tamaño variable, puede crecer o decrecer con las actualizaciones.
Un null solo almacena el tamaño (0).

Comparación entre LONGs y LOBs

LOB LONG
Varias columnas Una columna
Hasta 2 gigabytes Hasta 4 gigabytes
Retornan la ubicación Retornan los datos
Almacenan la ubicación (los segmentos están separados de los tablespaces, el acceso es aleatorio por pedazos por medio de interfaces del tipo de archivos) Se almacenan como una tabla vinculada (se almacenan como una lista vinculada, de manera que el acceso es secuencial)

Rowid

  • Identificador único para cada fila en la Base de Datos.
  • No se almacena como una columna con valor.
  • Se puede emplear para localizar una fila.
  • Medio más rápido para acceder a una fila de la tabla.
  • Se almacenan en los índices (asociado con un valor de clave).

Creación de tablas

CREATE TABLE [schema.] table
            (column datatype[, column datatype] . . .)
            [ TABLESPACE tablespace ]
            [ PCTFREE integer ]
            [ PCTUSED integer ]
            [ INITRANS integer ]
            [ MAXTRANS integer ]
            [ STORAGE storage-clause]
            [ LOGGING | NOLOGGING ]
            [ CACHE | NOCACHE ]
Guía para creación de las tablas:
  • Asigne las tablas a tablespace separados de aquellos que contienen segmentos de rollback, temporarios y de índices
  • Use pocas medidas de extensiones estándar, múltiplo de 5 veces el valor de DB_BLOCK_SIZE para minimizar la fragmentación
  • Para la visita de la tabla completa use el parámetro DB_FILE_MULTIBLOCK_READ_COUNT ==> cuantos bloques se leen en conjunto por el servidor
  • Use la cláusula CACHE para tablas pequeñas que se acceden frecuentemente.
Copia de una tabla existente:
CREATE TABLE new_emp
STORAGE ( INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
NOLOGGING
TABLESPACE data02
AS
SELECT * FROM scott.employees;
No se copian las restricciones, triggers y los privilegios sobre la tabla. Se copian las definiciones de columnas NOT NULL. El uso de NOLOGGING acelera la creación de la nueva tabla.
Efectos de los cambios en los parámetros de almacenamiento:
  • NEXT: tamaño que tendrán las próximas extensiones de la tabla (se redondea a un valor múltiplo de la medida del bloque más grande o igual al valor modificado).
  • PCTINCREASE: para recalcular el valor de NEXT cuando se asigna una nueva.
  • MINEXTENTS: debe ser menor o igual al valor actual de MINEXTENTS. No tiene efecto inmediato, se empleará cuando la tabla sea truncada.
  • MAXEXTENTS: debe ser mayor o igual que el valor actual de extensiones de la tabla.
  • El valor INITIAL no puede ser modificado.

Efectos de los cambios en PCTFREE y PCTUSED: PCTFREE: afecta las futuras inserciones. Los bloques llenos no se afectan hasta que no estén de nuevo en la lista de bloque libres. PCTUSED: afectan a todos los bloques de la tabla. En las actualizaciones o eliminaciones se verifica el espacio respecto del nuevo valor de PCTUSED. INITRANS: sólo afecta a los nuevos bloques. MAXTRANS: afecta a todos los bloques de la tabla.

Truncado de tablas

TRUNCATE TABLE [schema.] table
 [ ( DROP | REUSE) STORAGE]
  • Se borran todas las filas de la tabla.
  • No se generan datos de rollback (se comete implícitamente).
  • Los índice correspondientes también son truncados.
  • Una tabla que está siendo referenciada por una clave foránea no puede ser truncada.
  • No se disparan los triggers de eliminación cuando se emplea este comando.
  • Si se emplea la cláusula DROP (valor por omisión):
    • Todas las extensiones excepto MINEXTENTS se desasignan.
    • High water mark se ubica en el primer bloque de la tabla.
    • NEXT_EXTENT se actualiza al valor de MINEXTENTS.
  • Si se especifica la cláusula REUSE se retiene todo el espacio usado por las tablas.
  • Los índices se eliminan independientemente del uso de REUSE ó DROP.

Vistas

Una vista es una representación particularizada de los datos contenidos en otras tablas u otras vistas. Es el resultado de una consulta que se trata como una tabla.
Al igual que las tablas, pueden contener hasta 254 columnas.
Las vistas se pueden usar para consultas, y con algunas restricciones para inserción, actualización y eliminación.
Las operaciones que se realizan sobre las vistas afectan las tablas bases de las vistas y están sujetas a restricciones de integridad y triggers de las tablas bases.
Una vista no ocupa ningún espacio en el almacenamiento ni tampoco contiene datos.

Uso de las vistas

Las vistas constituyen una medida adicional de seguridad que permite restringir el acceso a un conjunto predeterminado de filas y/o columnas de las tablas.
Pueden utilizarse para esconder la complejidad de los datos, simplificar comandos del usuario, presentar los datos de una manera diferente, expresar consultas que no pueden expresarse sin el uso de una vista (por ejemplo: GROUP BY de una vista con una tabla o una UNION entre una vista y una tabla).
Son útiles para aislar a las aplicaciones de los cambios en la definición de las tablas.
Oracle almacena la definición de la vista en el diccionario de datos como el texto de la consulta que la define.

Vistas que pueden ser actualizadas

Una vista puede ser creada por cualquier consulta SQL pero para que pueda emplearse sentencias SQL UPDATE, INSERT o DELETE, la sentencia que la genera debe:
  • Realizarse sobre una sola tabla o,
  • si involucra más de una tabla la cláusula join no debe tener ninguna de éstas cláusulas: DISTINCT, GROUP BY, START WITH, CONNECT BY, ROWNUM, y ninguna operación de conjunto, como UNION o INSTERSECT.

Vistas de partición

Un administrador de BD puede emplear las vistas de partición para generar una vista de una tabla que ha sido particionada previamente y verla como si esta fuera una sola.
Ejemplo de la generación de 4 particiones de una tabla de VENTAS, 1 por ada trimestre y la creación de la vista global.
ALTER TABLE Q1_SALES ADD CONSTRAINT C0 check (sale_date < 'Apr-01-1995');
ALTER TABLE Q2_SALES ADD CONSTRAINT C1 check (sale_date >= 'Apr-01-1995' and sale_date < 'Jun-30-1995');

ALTER TABLE Q3_SALES ADD CONSTRAINT C2 check (sale_date >= 'Jul-01-1995' and sale_date < 'Sep-30-1995');

ALTER TABLE Q4_SALES ADD CONSTRAINT C3 check (sale_date >= 'Oct-01-1995' and sale_date < 'Dec-31-1995');

CREATE VIEW sales AS

 SELECT * FROM Q1_SALES UNION ALL 

 SELECT * FROM Q2_SALES UNION ALL 

 SELECT * FROM Q3_SALES UNION ALL

 SELECT * FROM Q4_SALES;

Secuencias

Se emplean para generar un único número secuencial. Es excelente para garantizar un número único que se puede emplear como clave en las tablas.
El generador de secuencias genera un serie de números secuenciales de hasta 38 dígitos.
Ejemplos
CREATE SEQUENCE seq_campus_site_id;
SELECT seq_campus_site_id.NEXTVAL FROM dual;
INSERT INTO campus_site VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Main Seattle');

Sinónimos

Un sinónimo es un alias para cualquier tabla, vista, snapshot, secuencia, procedimiento, función o paquete.
Los sinónimos se emplean frecuentemente por seguridad y conveniencia. Con ellos se puede lograr:
  • Enmascarar el nombre del dueño de un objeto.
  • Simplificar las sentencias SQL.
  • En aplicaciones distribuidas dar transparencia a la localización de las bases de datos.
  • Se pueden crear sinónimos públicos y privados.
Si existen varios sinónimos objetos con el mismo nombre y se realiza una transacción sobre ese objeto, ORACLE busca la información en el siguiente orden:
  1. Una tabla propia con ese nombre
  2. Un sinónimo privado con ese nombre.
  3. Un sinónimo público con ese nombre.

Índices

Los índices son estructuras opcionales asociados con tablas y clusters, que permiten recuperar la información de ellos más rápidamente.
Los índices son estructuras lógicas y físicas independientes de las tablas. Se pueden crear y eliminar en cualquier momento, sin ningún efecto en las tablas.
La presencia de muchos índices sobre una tabla disminuye la performance de la tabla cuando se actualiza, inserta o borra porque tienen que actualizarse los índices asociados con las tablas.
El índice consiste en un B-Tree que contiene el valor de la clave junto con los datos no claves:
  • Recorriendo el índice se puede acceder a todas las filas en el orden de la clave.
  • No se pueden definir restricciones UNIQUE, ni se pueden definir en un cluster.
  • No se pueden replicar ni distribuir.
  • Se pueden definir LOBs pero no LONGs.

Índices únicos

Los índices únicos garantizan que dos filas de las tablas no tengan valores duplicados en las columnas que definen el índice.
Oracle crea automáticamente índices únicos para las columnas correspondientes a claves primarias o cláusulas de restricción UNIQUE.

Índices compuestos

Es un índice que se crea con múltiples columnas.
Por lo general el orden es importante y la columna más selectiva debe ir primero.
No se pueden especificar más de 32 columnas en un índice regular.
Otros índices que ese pueden construir: Inversos, Bitmap y Tablas Organizadas por índices.

Índices inversos

  • Invierte los bytes de las columnas índices, excepto el ROWID.
  • Mejora la distribución de índices (ingreso en orden ascendente).
  • Búsquedas por igualdad (por rangos de valores no).

Índices Bitmap

  • Tablas con millones de filas y las columnas claves de baja cardinalidad.
  • Cuando las consultas emplean múltiples condiciones OR en la cláusula WHERE.
  • Cuando hay operaciones de sólo lectura y de baja actividad en las columnas clave.
Comparación entre índices normales y bitmap
Normales Bitmap
Aplicables a columnas con alta cardinalidad Aplicables a columnas con baja cardinalidad
La actualización de claves no es relativamente costosa La actualización de claves es costosa
Ineficiente para consultas con predicados OR Eficiente para consultas con predicados OR
Util para transacciones en línea Util para sistemas soporte de decisiones (DSS)

Guías para la creación de índices

  • Aceleran las operaciones de consultas, pero no las ABM.
  • Minimizar la cantidad de índices para tablas que sean muy volátiles.
  • Tablespaces separados.
  • Considerar el uso de NOLOGGING en la creación de los índices.
  • INITRANS índices > INITRANS para tablas.
Valor del PCTFREE
Sólo para la creación de índices -> para inserciones entre dos valores de índices.
Bajo para índices monótonos o de generación automática. Para los otros casos usar: (Máximo número de filas – Número de filas iniciales)/Máximo número de filas x 100

Triggers

Procesos que son ejecutados implícitamente cuando una sentencia INSERT, UPDATE o DELETE es ejecutada sobre la tabla asociada con el trigger.
Un trigger puede incluir sentencias SQL y PL/SQL para ejecutar como una unidad y puede llamar a procedimientos almacenados.
Los triggers se emplean en general para:
  • Generar automáticamente columnas derivadas.
  • Prevenir transacciones inválidas.
  • Forzar autorizaciones de seguridad complejas.
  • Forzar relaciones de integridad referencial a lo largo de los nodos de una base de datos distribuida.
  • Forzar reglas de negocios complejas.
  • Proveer eventos de logging transparentes.
  • Mantener el sincronismo sobre tablas replicadas.
  • Proveer auditorías sofisticadas.
  • Hacer estadísticas sobre el acceso de los datos.
ver más...

Clusters

Un cluster es un grupo de tablas que comparten el mismo bloque de datos porque comparten columnas comunes que frecuentemente se usan juntas.
Las tablas se debe asignare a un cluster siempre que se empleen juntas en la misma sentencia SQL con un join y condiciones de igualdad.
Los beneficios que ofrece el uso de clusters son:
  • Reduce el tiempo de acceso de los joins que se hagan sobre las tablas clustered.
  • En un cluster, el valor de la clave cluster es el valor de las columnas clave del cluster para una fila particular. Cada clave cluster se almacena solo una vez, en el cluster y en el índice, no importa cuantas filas diferentes tienen ese valor.
Una desventaja que tiene la creación de un cluster es que puede aumentar el tiempo de las operaciones INSERT, UPDATE o DELETE si se las compara con el almacenamiento en tablas independientes.

Clave cluster

La clave cluster es la columna, o grupo de columnas, que las tablas clustered tienen en común.
Por cada columna especificada en la clave cluster cada una de las tablas creadas en el cluster debe tener una columna que tenga la mismo tipo y tamaño de la columna que en la clave cluster.
No se pueden incluir en la clave cluster columnas LONG o LONG RAW.

Índice cluster

Se debe crear un índice para las columnas de la clave cluster después de creado el cluster.

Hash cluster

Hashing es un modo opcional de almacenar datos de una tabla para mejorar el tiempo de respuesta en la recuperación de la información.
Para usar hashing se debe crear un cluster hash y cargar las tablas dentro del cluster.
La función hash es la que se emplea para almacenar y recuperar la información. La clave hash puede constituirse por una o varias columnas.
Un hash cluster es una alternativa para tablas non-clustered con un índice o un cluster indexado.
Hashing se debe usar cuando:
  • Las tablas no varían en medida
  • El interés principal es optimizar la performance de una consulta
  • Las consultas son consultas por = en las columnas hash
  • Las claves son bien distribuidas por la función hash
Notas:
  • Para aplicar hash a una tabla primero debe crearse un cluster
  • Un cluster hash debe existir antes de crear una tabla que use el algoritmo de hash

Almacenamiento en un cluster hash

Un cluster hash almacena las filas relacionadas en el mismo bloque de datos. Las filas en un cluster hash se almacenan juntas basadas en el valor de hash.

Comparación entre el acceso en un cluster hash y un cluster de índices
Para la función hash se puede emplear una función interna (provista por Oracle) o una especificada por el usuario.

Asignación de espacio para un cluster hash

Como cualquier otro segmento la asignación de extents durante la creación de un cluster hash esta controlada por los parámetros de la cláusula STORAGE: INITIAL, NEXT y MINEXTENTS.
No obstante una porción de espacio inicial que se llama tabla hash es asignado en el momento de la creación de modo tal que todas las claves hash del cluster puedan incluirse.
Se asigna espacio adicional al cluster hash para los bloques de overflow que son necesarios cuando los bloques para filas se llenan.
Ejemplo: cuando se inserta una fila en una tabla cluster, y las filas correspondientes a la clave cluster se encuentran en un bloque que esta lleno, la fila no puede ser insertada en el bloque raíz, por lo tanto se genera un bloque de overflow que se “encadena” con el bloque original.

Vistas materializadas (snapshots)

Las vistas materializadas pueden ser utilizadas para sumarizar, precalcular, replicar y distribuir datos.
Los snapshots pueden ser de dos tipos: simples y complejos. Simples cuando los datos que se extraen corresponden a una sola tabla. Complejos cuando involucramos joins y otras cláusulas.
Los datos de un snapshot solo pueden ser seleccionados, no se pueden hacer INSERT, UPDATE o DELETE.
Los datos bajados pueden refrescarse, para ello se usan los snapshots log.
Ejemplo:
Create Snapshot current_sales
Tablespace data_warehouse
Storage (initial 900m next 100 m pctincrease 0)
Refresh Fast Next sysdate+7
As Select * From Buenos_aires.ventas.sales;

[editar] Procedimientos, funciones y paquetes

Los procedimientos y funciones son idénticos con la excepción que una función siempre retorna un valor a quien la llama mientras que el procedimiento no.
Beneficios del empleo de procedimientos y funciones:
  • Seguridad: Se puede restringir el acceso a los datos de la base de datos a través de los procedimientos y funciones solamente. Por lo tanto se da a un usuario el privilegio de ejecutar un determinado procedimiento y función que actualiza la BD, pero no se le da la posibilidad de actualizar directamente la BD.
  • Tiempo de respuesta: la compilación se realiza previamente a la ejecución; además si el procedimiento ya existe en la SGA, no es necesaria su recuperación del disco.
  • Asignación de memoria: se necesita una sola copia instalada en la memoria y éste puede ser usado por múltiples usuarios.
  • Productividad al diseñar las aplicaciones.
  • Integridad: mejoran la integridad y consistencia porque pueden ser testeados.
Un paquete es un conjunto de procedimientos y funciones relacionados, que junto con el cursor y las variables que usa, se almacena en la base de datos para uso continuado como una unidad.
Los paquetes consisten de dos partes: la especificación y el cuerpo. La especificación del paquete declara cuales son todas las construcciones que son públicas de un paquete, y el cuerpo define todas las construcciones (públicas y privadas) del paquete.
Ventajas de un paquete:
  • Encapsulamiento de los procedimientos relacionados y sus variables.
  • Declaración de variables, procedimientos, constantes y cursores públicos y privados.
  • Mejoran el tiempo de respuesta ya que un paquete instala todos los procedimientos y funciones incluidos en él en la memoria.




Manual del estudiante de Ingeniería en Sistemas de UTN/Bases de datos avanzadas/Integridad de los datos

 

Una restricción de integridad es un método declarativo para definir una regla sobre una columna o conjunto de columnas de una tabla.
Si cualquier resultado de una sentencia DML viola una restricción de integridad, ORACLE vuelve atrás la sentencia y retorna un error.
Las ventajas que presentan las restricciones de integridad sobre otros métodos para definir reglas (triggers, procedimientos, funciones) son:
  • Simplicidad de su definición.
  • Centralización de las restricciones de integridad: se definen sobre las tablas y se almacenan en el diccionario de datos, lo cual facilita la modificación.
  • Mejor performance: el optimizador de consultas de ORACLE se emplea cada vez que se define una regla de integridad.

Tipos de integridad

Nulls
Es una regla que permite o no la inserción o modificación de una fila con un valor nulo (null) sobre una columna.
Columnas con valor único (Unique values)
Permite la inserción o actualización de una fila solo si contiene un valor único sobre esa columna o conjunto de columnas.
Clave Primaria (Primary Key)
especifica que cada fila de la tabla puede ser identificada con los valores de la columna o conjunto de columnas).
Integridad Referencial
permite la inserción o actualización de una fila si el valor de la columna o conjunto de columnas, que se denomina el valor dependiente, tiene un valor igual en la tabla relacionada. La integridad referencial también incluye las reglas que dictaminan la manipulación de datos que está permitida sobre los valores referenciados y como estas acciones afectan los valores dependientes. Las reglas asociadas con la integridad referencial son:
  • Restrict: no permite la actualización o eliminación de los datos referenciados.
  • Set to null: cuando los datos referenciados se actualizan o eliminan, todos los datos dependientes se ponen en Null.
  • Set to default: cuando los datos referenciados se actualizan o borran, todos los datos dependientes se ponen en el valor default.
  • Cascade: cuando los datos referenciados se actualizan o eliminan, todos los datos dependientes correspondientes se actualizan o eliminan.
Chequeos de integridad
son reglas definidas por el usuario para una columna o conjunto de columnas que permiten o no la actualización, inserción o eliminación de una fila basado en el valor de la columna o conjunto de columnas.

Not null

Por omisión, todas las columnas en un a tabla permiten valores nulos. Una restricción de NOT NULL establece que ningún valor nulo pueda incluirse en una columna.

Unique key

Una restricción de integridad UNIQUE KEY sobre una columna o conjunto de columnas define que dos filas de la tabla no pueden contener los mismos valores en esa columna o conjunto de columnas.
Si la restricción de UNIQUE key abarca varias columnas se dice que el grupo de columnas tiene una clave única compuesta. Para que dos valores de una clave compuesta se consideren duplicados, los valores de TODAS sus columnas deben ser iguales.
Ejemplos de definición de restricciones de unicidad:
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50), 
CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name)
);
Utilizando ALTER:
ALTER TABLE supplier
add CONSTRAINT supplier_unique UNIQUE (supplier_id);
ORACLE obliga a que toda restricción de integridad de UNIQUE KEY contenga un índice, por lo tanto la restricciones de integridad de este tipo tienen las misma limitaciones impuestas a los índices compuestos: hasta 16 columnas y la medida en bytes de la clave compuesta no puede exceder aproximadamente la mitad de la medida del bloque de base de datos.
En la combinación de restricciones de clave única y valores nulos, se debe tener en cuenta que no se pueden tener iguales valores no-nulos en las columnas especificadas con restricciones UNIQUE KEY y valores nulos.

Primary key

Cada tabla de la base de datos debe tener a lo sumo una restricción PRIMARY Key. Los valores en el grupo de una o más columnas que están sujetas a esta restricción constituyen el identificador único de la fila. La principal diferencia con la restricción de UNIQUE Key es que las columnas de la clave primaria no pueden contener valores nulos.
ORACLE obliga a que toda restricción de integridad PRIMARY key contenga un índice.

Foreign key (Integridad Referencial)

Distintas tablas en una base de datos pueden estar relacionadas por columnas comunes.

Definiciones

Clave foránea
La columna o conjunto de columnas que hacen referencia a una clave referenciada. La tabla que contenga esta clave se llamará tabla dependiente.
Clave referenciada
la clave única (unique key) o la clave primaria (primary key) de la misma tabla o de un a tabla diferente que es referenciada por una clave foránea. La tabla que contenga esta clave se llamará tabla referenciada.
Una restricción de integridad referencial requiere que cada fila de la tabla dependiente contenga un valor de clave foránea que sea igual a un valor de clave única o primaria en la tabla padre.
El modelo relacional permite que el valor de una clave foránea o sea igual al valor de la clave referenciada, o sea nulo. Existen muchas interpretaciones sobre este hecho cuando se habla de claves foráneas compuestas. Las reglas que se pueden aplicar en este caso son:
match full
No se permiten claves foráneas con valores nulos parciales. O todos los componentes de la clave foránea deben ser nulos, o la combinación de valores contenidos en la clave foránea deben aparecer como clave primaria o única de una fila de la tabla referenciada.
match partial
Se permiten claves foráneas con valores nulos parciales. O todos los componentes de la clave foránea deben ser nulos, o la combinación de valores no-nulos contenidos en la clave foránea debe aparecer en la porción correspondiente como clave primaria o única de una fila de la tabla referenciada.
match none
se permiten valores de claves foráneas compuestas parcialmente nulas. Si cualquier columna de una clave foránea es nula, luego, la porción no-nula de la clave no tiene que tener un valor correspondiente en la clave padre.
En las restricciones de integridad referencial se deben especificar las acciones que se realizarán en las filas dependientes si una clave de la tabla padre es modificada. Las opciones que soporta Oracle son:
  • UPDATE RESTRICT
  • DELETE RESTRICT
Esta opción define que los valores de la clave referenciada no pueden ser actualizados o eliminados si los datos resultantes violan las restricciones de integridad referencial.
  • DELETE CASCADE
Esta opción especifica que cuando las filas que contienen la clave referenciada son eliminadas, todas las filas de las claves foráneas dependientes también serán eliminadas.
Las acciones que no están soportadas por las restricciones de integridad referencial deben ser realizadas por medio de triggers.

Check integrity

La verificación de una restricción de integridad sobre una columna o conjunto de columnas requiere que una condición especificada sea verdadera o desconocida para todas las filas de una tabla. Si una sentencia DML resulta que la condición de la verificación de integridad se evalúa en falsa la sentencia se vuelve atrás (rolled-back).
La condición tiene algunas limitaciones:
  • Debe ser una expresión booleana evaluada usando los valores de la fila que se inserta o actualiza.
  • No puede contener subconsultas, secuencias, ni funciones SQL SYSDATE, UID, USER o USERENV ni pseudocolumnas LEVEL o ROWNUM.
Una columna simple puede tener múltiples restricciones que se deben verificar y que contienen a la columna en su definición.

[editar] Difiriendo las verificaciones

Se pueden diferir las verificaciones de las condiciones para que se hagan al final de una transacción.
  • Una restricción es diferida (deferred) si el sistema chequea si la restricción se satisface cuando la transacción es cometida. Si la condición es violada entonces el commit hace que la transacción vuelva atrás (rolled back).
  • Si una restricción es inmediata (not deferred) esta se chequea al final de cada sentencia. Si es violada la sentencia se vuelve atrás inmediatamente.
Si una restricción causa una acción (por ejemplo, DELETE CASCADE) la acción es siempre realizada como parte de la sentencia que la causa, independientemente si la restricción es diferida o inmediata.
Los atributos de las restricciones que se pueden especificar en la cláusula CONSTRAINTS son:
  • DEFERRABLE o NOT DEFERRABLE
  • INITIALLY DEFERRED o INITIALLY INMEDIATE
Las definiciones de los restricciones de integridad se realizan en la creación de tablas (CREATE TABLE) o cuando se altera una tabla (ALTER TABLE) y se pueden declarar para la tabla o cuando se define una columna específicamente.
La especificación de los parámetros de los chequeos de integridad se realizan con la sentencia SET CONSTRAINTS donde se especifica si la restricción será un DEFERRED o INMEDIATE.



No hay comentarios :

Publicar un comentario

DEJA TU COMENTARIOS CON TUS DUDAS Y SUGERENCIAS,ASI COMO TAMBIEN UN PEDIDO EN PARTICULAR.
TAMBIEN PUEDES TU CORREO ELECTRONICO PARA UNA RESPUESTA MAS RAPIDA.