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.

Uso de la claúsula WITH

En el siguiente ejemplo encontraremos todos las divisiones de una empresa cuyos empleados tienen un salario medio un 10 por ciento por encima del salario medio de la empresa:

WITH salario_division AS (
  SELECT division,
         AVG(salario) salario_medio
  FROM   empleados
  GROUP BY division )
SELECT division,
       salario_medio
FROM   salario_division
WHERE  salario_medio > (
       SELECT AVG(salario_medio) * 1.1
       FROM   salario_division )
ORDER BY salario_medio;

Como se puede observar en la consulta existen dos bloques, una consulta principal y una subconsulta. Ambas necesitan realizar operaciones agrupando datos. Reescribamos la sentencia sin utilizar la claúsula WITH:

SELECT division,
       AVG(salario) salario_medio
FROM   empleados
GROUP BY division
HAVING AVG(salario) > (
       SELECT AVG(salario) * 1.1
       FROM   empleados )
ORDER BY AVG(salario);

Comparando ambas sentencias podemos concluir que al utilizar la claúsula SQL WITH y almacenar en una tabla temporal la consulta que hace el GROUP BY, evitamos que se tengan que agrupar los datos más de una vez. Este hecho debe hacernos pensar que el rendimiento de la sentencia que utiliza la claúsula WITH debe ser mejor que el de la sentencia que no lo utiliza.

Características de la claúsula WITH

a) Sólo se puede usar en sentencias SELECT.

b) Cuando se define una consulta con el mismo nombre de una tabla existente en la base de datos Oracle, puesto que el analizador sintáctico o parser (ver fases en la ejecución de una sentencia SQL) de las sentencias SQL o PLSQL busca de dentro a fuera, el nombre dentro de la claúsula WITH tendrá prioridad frente al nombre de la tabla.

c) Puede contener más de una consulta. Cada consulta se separa mediante comas. Las consultas definidas después de otras consultas pueden utilizar las definiciones previas.

Artículos relacionados:
Vistas materializadas o materializaed views.
Cursores PL/SQL.
Tablas externas en el SQL de Oracle.