|
La claúsula WITH se puede usar desde Oracle 9i, y permite asignar un nombre a una subconsulta. A continuación, se puede hacer referencia a la subconsulta en múltiples lugares de la consulta, especificando el nombre de la subconsulta. Oracle optimiza las consultas que usan WITH, ya sea como una vista o como una tabla temporal:
WITH
nombre_subconsulta_1 AS (SELECT ... )
nombre_subconsulta_2 AS (SELECT ... )
...
nombre_subconsulta_n AS (SELECT ... )
SELECT ... FROM nombre_subconsulta_1, nombre_subconsulta_2, ...;
El nombre de cada subconsulta definida en la claúsula WITH es visible para la consulta principal y todas las subconsultas posteriores, excepto para la propia subconsulta en cuestión.
Esta cláusula está sujeta a las siguientes restricciones:
- Sólo se puede indicar una claúsula WITH por consulta. Y no se pueden anidar claúsulas WITH, de modo q no se puede usar dicha claúsula en las subconsultas del propio WITH.
- En una consulta compuesta (UNION, UNION ALL, MINUS, INTERSECT) sólo se puede usar la claúsula WITH en el "from" de cada consulta de la citada consulta compuesta.
Ejemplo:
Sobre las tablas clásicas de ejemplo de Oracle (EMP y DEPT), vamos a obtener el listado de empleados, con su nombre, el nº de empleados q hay en su departamento, así como el nombre de su jefe y el nº de empleados del departamento al q pertenece el jefe.
En este caso usaremos una subconsulta (SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno;) para obtener el nº de empleados por departamento, q usaremos tanto para obtener el nº de empleados del departamento de cada empleado, como del jefe respectivo:
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc1,
emp m,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
En el plan de ejecución se observa cómo la subconsulta (q tiene un GROUP BY) se ejecuta 2 veces:
La anterior consulta la podemos resolver usando WITH, definiendo la subconsulta q se repite 2 veces. De esta forma, Oracle ejecutará una sola vez la subconsulta mejorando el plan de ejecución y el tiempo de respuesta. OJO, revisad siempre el plan de ejecución, y sobre todo el tiempo de respuesta real:
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
El plan de ejecución en este caso es mejor, bajando el coste de 59 a 37. Se observa q en este caso, Oracle transforma la subconsulta definida en la cláusula WITH en una tabla temporal (y ya sólo vemos un GROUP BY):
Aunque en este ejemplo el plan de ejecución usando WITH es mejor (menor coste) que con la consulta original, para el caso concreto del ejemplo, en la BD de desarrollo la tabla EMP tenía sólo 14 filas, de modo que no había diferencia en el tiempo de ejecución, pero insertando más filas en EMP (14336 filas), la sentencia con WITH pasa a tardar la mitad que sin WITH (9 segundos con WITH y 18 sin WITH).
Enlaces de interés:
|
