La claúsula WITH en SQL y PL/SQL

La versión 9i de las bases de datos Oracle permite el uso de la claúsula WITH en SQL y PLSQL. Este comando permite reusar una consulta SELECT cuando esta hay que utilizarla más de una vez en una sentencia o consulta SQL compleja. Los resultados de la consulta definida en la claúsula WITH son almacenados en una tabla temporal pudiendo de esta forma mejorar el rendimiento de la sentencia principal.

Aunque no siempre conseguiremos mejorar el rendimiento utilizando la claúsula WITH, lo que sin duda facilitaremos es la lectura y el mantenimiento del código PL/SQL o SQL. Dentro de la claúsula WITH daremos un nombre a las consultas SELECT a reutilizar (WITH admite la definición de múltiples consultas con sólo separarlas por comas), dicho nombre será visible para todas las consultas definidas posteriormente dentro del mismo WITH. Obviamente, también será visible para la sentencia o consulta principal.

Operaciones aritméticas con fechas y la función TRUNC del PL/SQL

Siguiendo con el tema de algunos de los artículos de este blog, hoy pretendemos dejar ya zanjado el tutorial sobre como podemos trabajar en PL/SQL con los campos tipo DATE y TIMESTAMP de las bases de datos Oracle. En esta ocasión empezaremos hablando de las operaciones aritméticas que podemos realizar con estos campos y terminaremos escribiendo sobre la función estándar del PLSQL TRUNC.

Las bases de datos Oracle permiten realizar una gran variedad de operaciones aritméticas con los tipos de datos DATE y TIMESTAMP, pudiendo realizar dichas operaciones de diversas maneras.

Funciones de grupo en SQL (SUM, AVG, COUNT, MAX y MIN)

Las llamadas group functions o funciones de grupo son funciones que operan sobre múltiples registros de una sentencia SELECT. En este artículo hablaré sobre las funciones de grupo más comunes del SQL (en un artículo posterior también hablaré sobre las cláusulas GROUP BY y HAVING muy relacionadas con este tipo de funciones).

Lo primero que hay que saber de las funciones de grupo es que hacen cálculos sobre un grupo de registros devolviendo un resultado único, por lo que permiten, por ejemplo, obtener totales.

Sentencia CASE en PL/SQL de Oracle

Las versiones de base de datos Oracle 9i y posteriores incluyen la posibilidad de utilizar la sentencia CASE dentro de una sentencia SQL (SELECT, UPDATE, etcétera). La sentencia CASE permite realizar las mismas operaciones que las sentencias de control PL/SQL IF-THEN-ELSIF-ELSE pero con la particularidad de que pueden utilizarse dentro de una sentencia SQL.

Enlos ejemplos que veréis a continuación entederéis rápidamente la gran utilidad de dicha sentencia. La sintaxis de la sentencia CASE es como sigue:

Los tipos de dato CHAR, VARCHAR2 y CLOB (Cadenas de caracteres en PL/SQL)

Después de haber hablado de los tipos numérico y de fecha del PL/SQL, llega el turno de empezar con las cadenas de caracteres. Lo primero que hay que saber al respecto es que una cadena de caracteres es una secuencia de símbolos pertenecientes a un conjunto particular de caracteres. En otras palabras, una cadena de caracteres puede estar compuesta por las letras del abecedario español, pero también podría estar compuesta por un conjunto de caracteres chinos o japoneses.

En PL/SQL existe tres tipos de cadenas de caracteres:

• De longitud fija: de manera que la cadena de caracteres se rellena con espacios en blanco si su longitud es menor que la declarada.
• De longitud variable: al definir la variable se declara la longitud máxima que puede alcanzar pero no se rellena con espacios si la longitud de la cadena es menor que la declarada. La máxima longitud de una variable de este tipo es de 32,767 caracteres.
• Objetos CLOB (Character Large Objects): se trata de un tipo especial del PLSQL que permite almacenar cadenas de caracteres de longitud variable de hasta 128 terabytes.

Análisis de la salida del comando TKPROF en PL/SQL y SQL

Ya hemos hablado en otro artículo acerca de las opciones y parámetros del comando TKPROF y de su uso para analizar los fichero de trazado PLSQL. Ahora es el momento de escribir acerca de cómo interpretar la salida de dicho comando. La salida del comando TKPROF muestra las estadíticas resultantes de la ejecución de una sentencia SQL o PLSQL agrupadas por fases de procesamiento. En el artículo "Pasos seguidos durante la ejecución de una sentencia SQL", ya hice una exposición detallada de lo que acontece en cada una de estas fases, no obstante, a continuación, voy a realizar un pequeño resumen.

Fases de procesamiento analizadas por el comando TKPROF

Fase de parsing (parse): Durante este paso se genera el plan de ejecución de la sentencia SQL o PL/SQL, se comprueban los permisos de ejecución y se verifica la existencia de los objetos referenciados en dicha sentencia (tablas, vistas, columnas, etcétera).

Tablas temporales en las bases de datos Oracle

Además de las tablas de la base de datos permanentes, Oracle permite la creación de tablas temporales para mantener datos propios y exclusivos a una sesión Oracle determinada. Estos datos permanecerán en el sistema sólo durante el tiempo que dure la transacción o sesión involucrada. No obstante, al igual que para las tablas permanentes, la definición de las tablas temporales se almacena en las tablas del sistema.

La sentencia CREATE GLOBAL TEMPORARY TABLE crea una tabla temporal Oracle cuya temporalidad puede ser definida a nivel de transacción (los datos existen mientras se realiza la transacción) o a nivel de sesión (los datos existen mientras dura la sesión). Los datos en una tabla temporal son propios y privativos de la sesión Oracle que la está utilizando. Una sesión Oracle determinada puede ver y modificar los datos que durante dicha sesión se insertaron en la tabla temporal, pero estos datos no son accesibles desde otra sesión diferente. Como es lógico, la sentencia LOCK no tiene efecto sobre las tablas temporales ya que cada sesión hace uso de sus propios datos.

Fases durante el procesamiento de una sentencia SQL

Durante el procesamiento de una sentencia SQL, ya sea mediante un script o un programa PL/SQL, se distinguen cuatro fases: análisis de la sintaxis (parsing), análisis de las variables (binding), ejecución (executing) y recuperación de datos (fetching).

Fase de parsing

Durante esta fase el servidor de la base de datos Oracle realiza las siguientes acciones:

• Busca la sentencia SQL en la memoria compartida (shared pool).
• Chequea la sintaxis de la sentencia siguiendo las especificaciones y la gramática del lenguaje SQL.
• Chequea la semántica, asegurando que los objetos Oracle referenciados en la sentencia SQL son válidos (existen en la base de datos) y satisfacen las restricciones de seguridad (es decir, el usuario que ejecuta la sentencia tienen los permisos adecuados sobre dichos objetos).
• Determina si el proceso que lanza la sentencia SQL tiene los permisos apropiados para ejecutarlo.
• Si la sentencia SQL incluye una vista (view) o una subquery (una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL), transforma dicha sentencia en una sentencia SQL equivalente e intenta simplificar la sentencia resultante.
• Determina y almacena el plan de ejecución o, si es posible, utiliza un plan de ejecución existente.

Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos Oracle

El almacenamiento caché de subconsultas o subqueries PL/SQL se trata de una funcionalidad de las bases de datos Oracle, denominada en inglés scalar subquery caching, que se encarga de optimizar internamente la ejecución de aquellas consultas que incorporan subconsultas. El funcionamiento es bastante intuitivo, si durante la ejecución de una consulta PLSQL compleja, dicha consulta incluye alguna subquery, la base de datos Oracle intentará almacenar en la caché la salida de dicha subconsulta con el objetivo de poder reutilizar dichos datos, una y otra vez, durante la ejecución de la consulta PL/SQL principal. Obviamente esto será mucho mejor para el rendimiento de la base de datos que el tener que re-ejecutar la subconsulta múltiples veces.

Los resultados de la subconsulta quedan almacenados en una estructura de datos interna o hash table que, mientras dura la ejecución de la consulta PLSQL, queda residente en la memoria caché de la sesión Oracle correspondiente. Dicha estructura de datos desaparece de la caché en el momento que la consulta PL/SQL termina.

Acceso remoto mediante DBLINK a una base de datos Oracle

La manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

Los DBLinks se crean en la base de datos local utilizando el comando PL/SQL o SQL CREATE DATABASE LINK. Obviamente, el usuario que ejecute dicho comando debe tener los permisos necesarios para poder hacerlo. La sintaxis del comando SQL mencionado es como sigue: