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.

Archivo:Hashvsindex.svg

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;

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.