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:

Puesta a punto de sentencias SQL (Tuning PLSQL 1)

Si la siguiente sentencia SQL SELECT:

SELECT *
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil = 'S' -- Soltero

Nos está dando tiempos de ejecución largos, esto querrá decir que:

   1. Obviamente la tabla empleados es de un tamaño considerable.

   2. La tabla no está adecuadamente indexada o que, aún habiéndose creado el índice adecuado, lógicamente un índice sobre la columna "nombre", el optimizador SQL decide utilizar el índice sobre otra columna.

Los bloques de código PL/SQL

Los bloques PL/SQL son unidades de código que se envían al servidor para que sean procesadas conjuntamente, obviamente esto es mejor para el rendimiento del sistema que si se envían las sentencias SQL separadamente una a una. Es decir, cuando se requiere la ejecución de un bloque PL/SQL, este es enviado al servidor PL/SQL donde se ejecuta el código PL/SQL y las sentencias SQL son enviadas al servidor Oracle, como el servidor PL/SQL reside en el servidor Oracle, el código se ejecuta eficientemente.

Secciones de un bloque PL/SQL

Un bloque de código PL/SQL contiene las siguientes secciones:

   1. La sección de declaraciones donde se definen los distintos objetos que se van a utilizar en el bloque (variables, constantes, cursores, etcétera). Esta sección es opcional.
   2. La sección de instrucciones. Sección obligatoria que contiene las sentencias ejecutables de SQL y PL/SQL.
   3. La sección de excepciones. Es una sección opcional donde se tratan todos los errores que puedan surgir a la hora de ejecutar las sentencias SQL y PL/SQL de la sección de instrucciones.