En ocasiones necesitamos guardar datos en forma temporal, esos datos solamente nos son útiles durante un rato y luego ya son desechables. Para esas ocasiones es que podemos usar las tablas temporales.
¿Y por qué no usar una tabla normal y escribir un «DELETE FROM MiTabla» antes de insertarle datos? De esa manera obtendríamos los mismos resultados que usando una tabla temporal, ¿verdad?
Sí y no.
Si un solo usuario necesita de esos datos entonces sí, usar una tabla temporal o usar una tabla normal a la cual le borramos todas las filas antes de empezar el proceso sería exactamente lo mismo.
Pero la cuestión se complica cuando son dos o más los usuarios que necesitan usar esa tabla, por ejemplo si usamos tablas normales podría ocurrir algo como esto:
- El usuario 1 borró todas las filas de la tabla
- El usuario 1 le insertó algunas filas a la tabla
- El usuario 1 hizo un COMMIT
- El usuario 2 borró todas las filas de la tabla
- El usuario 2 le insertó algunas filas a la tabla
- El usuario 2 hizo un COMMIT
- El usuario 1 quiere consultar las filas de la tabla …. y no encuentra las filas que él insertó, sino las filas que insertó el usuario 2
En cambio, si se usan tablas temporales algo como lo anterior jamás podría ocurrir porque cada usuario tiene su propia versión de la tabla temporal. Es decir que lo que hace el usuario 1 es totalmente independiente de lo que hace el usuario 2. El usuario 1 puede hacer lo que se le antoje en la tabla temporal que el usuario 2 jamás se enterará. Y viceversa.
A las tablas temporales en Firebird se las conoce como GTT (Global Temporary Table) y pueden ser de dos clases:
- Confinadas a la transacción
- Confinadas a la conexión
«Confinadas a la transacción» significa que la tabla temporal solamente puede tener filas mientras está siendo usada en una transacción. Cuando la transacción termina todas esas filas son automáticamente borradas definitivamente, sin importar como haya finalizado la transacción (con un COMMIT o con un ROLLBACK) porque en ambos casos todas las filas de la tabla temporal son borradas.
«Confinadas a la conexión» significa que la tabla temporal solamente puede tener filas mientras dure la conexión actual. Cuando la actual conexión con la Base de Datos termina, todas las filas de la tabla temporal son borradas definitivamente.
¿Cómo se crea una tabla temporal?
Escribiendo el comando «CREATE GLOBAL TEMPORARY TABLE MiTabla», definiendo las columnas y luego finalizando con la cláusula «ON COMMIT DELETE ROWS» (si queremos una tabla temporal confinada a la transacción) o con la cláusula «ON COMMIT PRESERVE ROWS» (si queremos una tabla temporal confinada a la conexión).
¿Las tablas temporales pueden tener Primary Key?
Sí
¿Las tablas temporales pueden tener Foreign Key?
Sí, pero limitadamente. Una tabla temporal no puede referenciar a una tabla normal. Tampoco una tabla temporal confinada a la conexión puede referenciar a una tabla temporal confinada a la transacción, por lo tanto las Foreign Keys posibles son:
- De una tabla temporal confinada a la transacción a una tabla temporal confinada a la transacción
- De una tabla temporal confinada a la conexión a una tabla temporal confinada a la conexión
- De una tabla temporal confinada a la transacción a una tabla temporal confinada a la conexión
¿Las tablas temporales pueden tener Unique Key?
Sí
¿Las tablas temporales pueden tener índices?
Sí
Ejemplo 1:
CREATE GLOBAL TEMPORARY TABLE TEMP ( TEM_IDENTI D_IDENTIFICADOR NOT NULL, TEM_NOMBRE D_NOMBRE40) ON COMMIT DELETE ROWS; ALTER TABLE TEMP ADD CONSTRAINT PK_TEMP PRIMARY KEY (TEM_IDENTI);
Aquí, creamos una tabla temporal llamada TEMP la cual estará confinada a la transacción. O sea que cuando la transacción finalice con un COMMIT o con un ROLLBACK todas las filas de esta tabla desaparecerán. También le agregamos una Primary Key.
Ejemplo 2:
CREATE GLOBAL TEMPORARY TABLE TEMP2 ( TEM_IDENTI D_IDENTIFICADOR NOT NULL, TEM_NOMBRE D_NOMBRE40) ON COMMIT PRESERVE ROWS; ALTER TABLE TEMP2 ADD CONSTRAINT PK_TEMP2 PRIMARY KEY (TEM_IDENTI);
Aquí, creamos una tabla temporal llamada TEMP2, la cual estará confinada a la conexión. O sea que cuando la conexión con la Base de Datos finalice todas las filas de la tabla TEMP2 serán eliminadas.
Verificando que las filas de las tablas temporales confinadas a la transacción son eliminadas
Para verificar que tanto un COMMIT como un ROLLBACK eliminan a todas las filas de la tabla temporal confinada a la transacción escribimos lo siguiente:
Captura 1. Si haces clic en la imagen la verás más grande
Como puedes ver, el primer SELECT mostró todas las filas insertadas pero el segundo SELECT no, ¿por qué? porque se escribió un COMMIT antes de él y con eso se finalizó la transacción. O sea que el segundo SELECT ya está en otra transacción.
Captura 2. Si haces clic en la imagen la verás más grande
En este caso se escribió un ROLLBACK antes del segundo SELECT y el resultado fue el mismo que obtuvimos al escribir el COMMIT, o sea: ninguna fila mostrada. Lo cual significa que tanto si la transacción finaliza con un COMMIT o con un ROLLBACK todas las filas de la tabla temporal son eliminadas. El segundo SELECT ya está en otra transacción porque la transacción anterior finalizó con el ROLLBACK.
¿En qué casos es recomendable usar tablas temporales?
Cuando te facilitan la vida. El caso típico es cuando debes realizar una consulta que involucra a varias tablas normales pero esa consulta es demasiado complicada, tiene un montón de condiciones en el WHERE o en el HAVING, y muchos JOINs o UNIONs y te cuesta llegar al resultado deseado porque tu SELECT nunca te muestra lo que te debería mostrar, por más que lo intentas y lo intentas, nunca obtienes lo que deberías obtener y ya te da dolor de cabeza. Entonces, en lugar de estar lidiando con un SELECT inmenso, que te marea de solo mirarlo, es mucho más conveniente filtrar el contenido de las tablas involucradas, colocarlos en algunas tablas temporales y luego con unos pocos JOINs obtienes lo que buscabas.
Al usar tablas temporales seguramente escribirás más, pero todo lo que escribas será fácilmente entendible y entonces obtener las filas deseadas será muy rápido.
Entonces ¿tu SELECT involucra a varias tablas normales, las condiciones del WHERE o del HAVING son muchas y no obtienes lo que deseas? Empieza a pensar en usar tablas temporales.
Artículo relacionado:
Alex
Ene 24, 2014 @ 01:13:54
Hola Walter, quiero felicitarte por tu blog, estoy pensando en pasar vistas a tablas temporales ya que actualmente estas mismas guardan mucha información y obviamente expongo el código de los querys, solo quería preguntarte si la creacion de estas es parecido a los views vi tus ejemplos pero donde puedo poner los select de las vistas ya que son muchos joins y where como lo comentas de antemano muchas gracias por tus comentarios.
wrov
Ene 24, 2014 @ 01:45:32
Disculpa, pero no te entiendo. ¿Pasar vistas a tablas temporales? No sé cual sería el beneficio que podrías obtener. Las vistas no guardan información, sólo la muestran. Y si quieres ocultar el código fuente de las vistas lo que puedes hacer es crear stored procedures seleccionables, compilarlos, y luego borrar el código fuente de esos stored procedures porque el Firebird ejecuta el código compilado, el código fuente solamente es útil para nosotros los seres humanos pero el Firebird no lo utiliza.
Desde luego que deberías tener copias de seguridad del código fuente de tus stored procedures, eso es elemental.
Saludos.
Walter.
Darío
Sep 25, 2015 @ 20:15:28
Hola Walter,
Muy buena explicación en todos tus Posts.
Me gustaría saber si se puede crear una tabla temporal a partir de una tabla existente. Por ejemplo, en MySQL se puede hacer esto con:
CREATE TEMPORARY TABLE t1 ( select * from t2);
Gracias y Saludos!
wrov
Sep 26, 2015 @ 11:18:43
Gracias Darío por el comentario positivo.
La respuesta es sí y no, te explico:
Firebird no dispone de tal comando nativamente pero puede emulárselo muy fácilmente.
Por ejemplo, en ISQL.EXE puedes escribir:
Pero fallará si has nombrado a la Primary Key, no te copiará las Foreign key ni los Check ni los Unique ni los índices y por supuesto tampoco los datos.
Así que en realidad no es de mucha utilidad.
Si usas un programa de administración gráfico (y deberías usar) tal como el IbExpert, el FlameRobin, el EMS SQL Manager, etc. entonces tendrás esa opción disponible.
Por ejemplo, si usas el EMS SQL Manager estos son los pasos a seguir:
1. Te conectas a la Base de Datos
2. Expandes «Tables» para ver los nombres de todas las tablas
3. Haces clic con el botón derecho sobre el nombre de la tabla que quieres duplicar
4. Haces clic en la opción: Duplicate table ‘MiTabla’
5. Eliges la Base de Datos en la cual se copiará la tabla
6. Eliges el nombre de la nueva tabla
7. Eliges si quieres copiar solamente los metadatos o los datos también
8. Eliges si quieres desactivar los triggers mientras se copia
9. Eliges si quieres copiar los privilegios
10. Copias la tabla
Aunque te parezcan muchos pasos, los haces en unos cuantos segundos después de practicarlos unas cuantas veces, porque todo es muy fácil.
Saludos.
Walter.
Victor
Oct 02, 2015 @ 09:02:25
Excelente aporte, solo una consulta, es posible utilizar triggers con las tablas temporales?
Saludos,
Victor
José Villalba
Mar 08, 2016 @ 07:57:37
Hola Walter.. buenísimo está tu blog..
Estoy entendiendo cada ves más el tema de las tablas temporales..
ya me mencionaron que para una transacción de ventas que estoy trabajando
necesito uno de ello, es decir, una tabla temporal.. eso sería una tabla donde esten los campos de Artículos, Clientes, Proveedores, Vendedores también..?? me gustaría contactar contigo Walter, porque necesito ayuda.. jose111987@gmail.com es mi mail por si dispongas de tiempo.
Desde ya muchisimas gracias..
wrov
Mar 08, 2016 @ 16:39:04
Hola José
Gracias por el comentario positivo sobre el blog.
No siempre se usan las tablas temporales, eso depende del diseño de la Base de Datos, son una facilidad que nos provee el Firebird y por eso existen, pero usarlas o no usarlas ya depende de cada quien y de cada caso.
Saludos.
Walter.