La claúsula WITH

De Jorge Niño -
La claúsula WITH en SQL y PL/SQL

Esposa reclama la atención del programador PL/SQL con la claúsula WITH del 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 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.

Comentarios

Publicar un comentario

Entradas populares de este blog

Actualizar las estadísticas

De Jorge Niño -
Cuando calcular estadisticas? De nuevo, no existe una regla sobre cada cuando se deben de calcular; pero algunos consejos son por ejemplo si se insertan/borran/actualizan un gran numero de registros a una tabla, tal vez millones, entonces inmediatamente despues hay que calcular debido a que si puede afectar los planes de ejecucion ya que se hizo un gran cambio. Si se desea hacer de manera automatica entonces depende de la carga de la base de datos, de la aplicacion, puede haber casos que semanalmente esta bien o hay veces que 1 vez al mes. Las estadisticas pueden actualizarse de dos maneras 1. Usando el comando sql ANALYZE 2. usando el paquete DBMS_UTILITY Usando el comando sql ANALYZE: Para actualizar las estadísticas de una tabla y todos sus índices, se debe ejecutar la siguiente sentencia: ANALYZE TABLE nombre_de_la_tabla COMPUTE STATISTICS; Para actualizar las estadísticas unicamente de la tabla y no de los índices, ejecutar: ANALYZE TABLE nombre_de_la_tabla COMPUTE STATISTI...
Leer más

Operaciones con fechas

De Jorge Niño -
Aqui algunos ejemplos para adicionar y sustraer días y meses y encontrar la diferencia entre fechas en Oracle. Estos ejemplos toman el resultado de la tabla "dual. La tabla Dual es una tabla virtual que existe en todas las Bases de datos Oracle. Muchas veces hemos usado la consulta SELECT sysdate FROM dual; la cual simplemente nos retorna la fecha y hora actual. Ajustar Dias, Semanas, Horas y minutos Para adicionar y sustraer días a una fecha, simplemente usamos los signos + o - respectivamente. Algunos ejemplos: SQL> SELECT sysdate + 7 FROM dual; SYSDATE+ ------- 25/09/06 SQL> SELECT sysdate - 30 FROM dual; SYSDATE- -------- 19/08/06 SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual; TO_CHAR(SYSDATE-14, ------------------- 09/04/2006 11:41 AM En el primer ejemplo, vemos que la consulta retorna la fecha siete días apartir de hoy. La segunda retorna la fecha de hace 30 días. En la tercera, se ha usado la función de c...
Leer más

Subconsultas

De Jorge Niño -
Definición de subconsultas. Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT . Normalmente se utilizan para filtrar una clausula WHERE o HAVING con el conjunto de resultados de la subconsulta, aunque también pueden utilizarse en la lista de selección. Por ejemplo podriamos consultar el alquirer último de un cliente. SELECT CO_CLIENTE, NOMBRE, MARCA, MODDELO FROM ALQUILERES WHERE CO_CLIENTE = 1 AND FECHA_ALQUILER = ( SELECT MAX (FECHA_ALQUILER) FROM ALQUILERES WHERE CO_CLIENTE = 1) En este caso, la subconsulta se ejecuta en primer lugar, obteniendo el valor de la máxima fecha de alquier, y posteriormente se obtienen los datos de la consulta principal. Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis. La subconsulta se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HA...
Leer más