Cláusula WITH y hint MATERIALIZE

Hace unos días me llegó una consulta con una cláusula 'WITH' que presentaba problemas con la ejecución, tomaba demasiado tiempo.

Para los que no están familiarizados, aquí explico cómo funciona la cláusula WITH.

Cláusula WITH

La cláusula WITH nació en el estándar SQL 99 y es un poco críptico ya que se presenta con una sintaxis reversa de los queries normales, se utiliza para mejorar las consultas anidadas.

Utilizando el ejemplo de Oracle con el esquema SCOTT, aquí estan los queries:

SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc
         ON e.deptno = dc.deptno;

Modificando para utilizar WITH queda así:

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN dept_count dc ON e.deptno = dc.deptno;

Aunque en esta consulta la diferencia es insignificante, a contunuación vemos una consulta donde se aprecia la ventaja de usar WITH:

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
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc1
         ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc2
         ON m.deptno = dc2.deptno;

Modificándolo para utilizar WITH queda:

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
       JOIN dept_count dc1 ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN dept_count dc2 ON m.deptno = dc2.deptno;

Como vemos, de esta forma la consulta hace uso del alias en lugar de repetir la subconsulta. También se puede utilizar para construir consultas más limpias y además se puede utilizar con instrucciones UPDATE o INSERT.

Materialize

Es una cláusula de optimizador no documentada MATERIALIZE que nos sirve cuando la consulta interna incluye tablas grandes.

Se utiliza agregando el hint /*+ MATERIALIZE */ a nuestra consulta, lo que hace que Oracle cree una tabla temporal mientras se ejecuta el query.

Ejemplo:

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ 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
       JOIN dept_count dc1 ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN dept_count dc2 ON m.deptno = dc2.deptno;

El uso de esta cláusula sólo es útil cuando el subconjunto de datos es lo sufucientemente grande para justificar el uso de una tabla temporal.

Espero este consejo les sea útil cuando hagan consultas con tablas grandes.