With as Select

Recientemente he encontrado una ayuda para escribir consultas complejas, o lo que es mejor, escribir menos para hacer lo mismo.

La nueva estructura es WITH AS SELECT y he aquí un ejemplo:

With dia As
(Select TRUNC (SYSDATE, 'D') d
From DUAL)
Select TO_CHAR (d, 'D'), TO_CHAR (d, 'DAY'), TO_CHAR (d)
From dia;

(Esta consulta te permite ver de un plumazo cual es el primer día de la semana en una bbdd cuya configuración desconoces y no sabes nada de los parametros NLS)

El WITH nos permite definir en la parte superior tantas subconsultas como necesitemos para simplificar el código y utilizar sus alias libremente en la consulta principal. Ideal para quien no le gusta escribir dos veces la misma cosa.

Respecto al rendimiento: Oracle se esfuerza por optimizarla reescribiendola como una vista en linea o una tabla temporal. Es decir, tan bueno o malo como el rendimiento del original.

Otro ejemplo típico viene en los manuales de oracle:

WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;

DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales 313800
Shipping 156400