9.4. CREAR TABLAS TEMPORALES

CREAR Y UTILIZAR TABLAS TEMPORALES.

Una tabla temporal tiene una definición que es visible a todas las sesiones, pero las filas dentro de ella son privadas a la sesión que la creo. La sintaxis es:

CREATE GLOBAL TEMPORARY TABLE temp_tab_name

(column datatype [,column datatype…] )

[ON COMMIT {DELETE | PRESERVE} ROWS] ;

La definición de columnas es la misma como una tabla regular, y pueden ser obtenidas de una consulta. La clausula opcional en el final determina el tiempo de vida útil de cualquiera de las filas insertadas. El valor por default es para eliminar las filas en el momento de la transacción que las inserta completa, pero este comportamiento puede ser cambiado para conservar hasta que la sesión que los inserto finalice. Cualquier opción elegida, los datos serán privados para cada sesión: diferentes usuarios pueden insertar sus propias filas en su propia copia de la tabla, y nunca verán las filas de los demás.

En muchos sentidos, una tabla temporal es similar a una tabla permanente. Usted puede ejecutar cualquier DML o comando SELECT contra esta. Puede tener índices, Constraints, y Triggers definidos. Puede ser referenciada en vistas y sinónimos o unida con otras tablas. La diferencia es que los datos son transitorios y privados a la sesión, y que todo comando SQL en contra mucho más rápido que comando contra tablas permanentes. 

La primera razón por la velocidad es que las tablas temporales no son segmentos en Tablespaces permanentes. Idealmente, solo existen en la PGA de la sesión que está utilizándola. Así no hay actividad en disco o incluso en el Database buffer Cache de actividad en cuestión. Si la PGA no puede crecer suficientemente para almacenar la tabla temporal (que será el caso sí millones de filas están siendo insertadas-no es inusual en la generación de reportes complejos), entonces la tabla consigue ser escrita a un segmento temporal en el Tablespace temporal del usuario. Entrada/Salida sobre Tablespace temporal es mucho más rápido que entrada/salida sobre Tablespace permanentes, porque no va vía Database Buffer Cache. Sino que todo lo realiza directamente  sobre el disco por la sesión de proceso servidor.

La segunda razón para la velocidad es que DML contra tablas temporales no genera Redo. Puesto que los datos solo persisten por la duración de una sesión (quizás solamente para la duración de una transacción), no hay un propósito en la generación redo. Esto da un doble beneficio de la rapidez de DML para la sesión de trabajo sobre la tabla, y tomando la tensión del sistema de la tensión de generación de redo, que puede ser un mal punto de contención en las bases de datos multiusuario ocupado.

La figura muestra la creación y uso de una tabla temporal con SQL*PLUS. El Database Control Tabla Creation Wizard puede también crear tablas temporales. Un típico uso de las tablas temporales es para los sistemas de reporte que ejecutan en Data Warehouse. Una gran cantidad de datos pueden ser extraídos desde las tablas fuentes y cargados en tablas temporales, donde puede ser manipulado en una forma adecuada para ejecutar reportes. En sistemas tales como estos, las tablas temporales pueden contener billones de filas y ocupar muchos gigabytes de espacio en Tablespaces temporales, pero en el momento de la salida de la sesión, todos los datos serán limpiados.

EJERCICIO 9-4.

CREAR Y UTILIZAR TABLAS TEMPORALES.

En este ejercicio, creara una tabla temporal para ser utilizada para reportar los actuales empleados. Demostrar, por medio de dos sesiones SQL*PLUS, que los datos son privados para cada sesión.

1. Conectarse a su Base de Datos con SQL*PLUS como usuario HR.

2. Crear una tabla temporal como sigue:

create global temporary table tmp_emps on commit preserve

rowsas select * from employees where 1=2;

3. Insertar algunas filas y confirmarla:

insert into tmp_emps select * from employees where

department_id=30;

commit;

4. Iniciar una segunda sesión SQL*PLUS como HR.

5. En la segunda sesión, confirme que el primer insert no es visible, incluso aun confirmado, e inserte algunas filas diferentes:

select count(*) from tmp_emps;

insert into tmp_emps select * from employees where

department_id=50;

commit;

6. En la primer sesión, truncar la tabla:

truncate table tmp_emps;

7. En la segunda sesión, confirmar que todavía hay filas en esa sesión copia de la tabla:

select count(*) from tmp_emps;

8. En la segunda sesión, demostrar que terminando la sesión se borran las filas. Para ello será necesario desconectar y conectar de nuevo:

disconnect;

connect hr/hr

select count(*) from tmp_emps;

9. Ordenar el medio ambiente al dejar caer las tablas, ya sea con SQL * Plus

drop table tmp_emps;

drop table ex_emps;

o con Database Contro. Los índices y Constraint tiene que se han eliminado también.

DENTRO DEL EXAMEN

Los objetivos del examen para el primer examen OCP especifica solo el conocimiento de tablas permanentes y temporales, índices y Constraint. Sin embargo, los candidatos también se puede esperar que estén familiarizados con vistas, sequencias y synonymous. Los seis de estos objetos de esquemas son cubiertos en el primer examen SQL: los exámenes DBA asumen que los candidatos tiene ya pasado este.

La sintaxis para la gestión de objetos de esquema puede ser muy incómodo. Por esta razón, puede ser tentador utilizar los asistentes en la base de datos de control para crear y modificar objetos. Esto está bien, si siempre haga clic en el botón Mostrar SQL para ver las instrucciones SQL generadas por los asistentes. La sintaxis se pondrá a prueba, y es vital que esté familiarizado con él.

RESUMEN DE CERTIFICACION.

Los datos son almacenados en tablas. Asociado a las tablas son los Constraints que controlan la estructura relacional de las tablas de la Base de Datos, que son definidas por medio de Constraints Foreign, Unique y Primary Key. Junto con los Constraints Check (incluyendo el Constraint Not Null), estos pueden hacer cumplir las reglas de negocio.

Los Constraint puede ser forzadas o en suspendido, y si se aplican hay control sobre el momento que se comprueban. Por defecto, todos los Constraints son forzados y comprueban en tiempo de ejecución de la declaración. Una violación al Constraint por lo tanto causara que la declaración sea regresada.

Los índices tienen una doble función: hacer cumplir  Constraints Foreign, Unique y Primary Key, y para mejorar el rendimiento de consultas. La creación puede ser automática: provocada por la creación de un Constraint. Sin embargo, si los índices son definidos manualmente  hay varias opciones que levan a hacer útiles.

Tablas temporales trabajan en muchas maneras como las tablas temporales. Pero los datos en ellas es privado para la sesión. Debido a que no existen como segmentos en un espacio de tablas permanente, pero sólo en el PGA sesiones y en los segmentos temporales en un espacio de tablas temporales, las operaciones en ellos son mucho más rápidos.

DOS MINUTOS

Crear y Modificar Tablas.

• Las tablas son objetos de esquema, comparten un namespace con vistas y synonyms.

• Después de la creación, definiciones de columnas puede ser agregadas, eliminadas y modificadas.

Gestionando Constraints.

• Un Constraints puede ser definido en tiempo de creación de la tabla, o agregado posteriormente.

• Un Primary Key es funcionalmente  equivalente a Unique Plus Not Null.

• Un Constraints Unique no para la inserción de muchos valores nulos.

• Constraint Foreign Key define la relación entre dos tablas.

Crear Índices.

• Los índices son requisito para hacer cumplir Constraints Unique y Primary Key.

• NULL no son incluidos en un índice B*TREE pero son incluidos en un índice Bitmap.

• Índices B*TREE pueden ser Unique o Non-Unique, que determina si puede aceptar valores llaves duplicados.

• Índices B*TREE son adecuados para columnas de alta cardinalidad, índices Bitmap para columnas de baja cardinalidad.

• Indices Bitmaps pueden ser Compound, function based, or descending; indices B*TREE tambien pueden ser unique, compress y reverse key.

Crear y Usar Tablas Temporales.

• Filas en una tabla temporales son visibles solo a la sesión que los inserto.

• DML sobre tablas temporales no generan redo.

• Tablas temporales existen solo en la PGA de la sesión o en un segmento temporal.

• Una tabla temporal puede mantener las filas de la duración de una sesión o de una transacción, dependiendo de cómo se creó.