Cláusula WITH : Factorización de subconsultas en Oracle

Base de datos, DBA-Avanzadas, Oracle, SQL
  • Preparar
  • Factorización de subconsultas
  • MATERIALIZE Hint
  • PL/SQL Sección de Declaración

Preparar

Los ejemplos siguientes utilizan las siguientes tablas.

-- DROP TABLE EMP PURGE;
-- DROP TABLE DEPT PURGE;

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

Factorización de subconsultas

La cláusula WITH, o cláusula de factorización de subconsultas, es parte del estándar SQL-99 y se agregó a la sintaxis SQL de Oracle en Oracle 9.2. La cláusula WITH puede procesarse como una vista en línea o resolverse como una tabla temporal. La ventaja de este último es que las referencias repetidas a la subconsulta pueden ser más eficientes ya que los datos se recuperan fácilmente de la tabla temporal, en lugar de ser consultados por cada referencia. Debe evaluar las implicaciones de rendimiento de la cláusula WITH caso por caso.
Este artículo muestra cómo se puede utilizar la cláusula WITH para reducir la repetición y simplificar declaraciones SQL complejas. No estoy sugiriendo que las siguientes consultas sean la mejor manera de recuperar la información requerida. Simplemente demuestran el uso de la cláusula WITH.
Utilizando el esquema de SCOTT, para cada empleado queremos saber cuántas otras personas hay en su departamento. Usando una vista en línea, podríamos hacer lo siguiente.

-- No-ANSI Syntaxi
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;

-- ANSI Syntax
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;
Usando una cláusula WITH, esto se vería como lo siguiente.
-- Non-ANSI Syntax
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,
       dept_count dc
WHERE  e.deptno = dc.deptno;

-- ANSI Syntax
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;

La diferencia parece bastante insignificante aquí.
¿Qué pasa si también queremos retirar el nombre del gerente de cada empleado y la cantidad de personas en el departamento de gerentes? Usando la vista en línea, ahora se ve así.

-- Non-ANSI Syntax
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;

-- ANSI Syntax
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;

Usando la cláusula WITH, esto se vería como lo siguiente.

-- No-ANSI Syntaxi
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;

-- ANSI Syntaxi
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;

Por lo tanto, no es necesario volver a definir la misma subconsulta varias veces. En su lugar, solo usamos el nombre de la consulta definido en la cláusula WITH, lo que hace que la consulta sea mucho más fácil de leer.
Si el contenido de la cláusula WITH es lo suficientemente complejo, Oracle puede decidir resolver el resultado de la subconsulta en una tabla temporal global. Esto puede hacer que múltiples referencias a la subconsulta sean más eficientes. Las sugerencias del optimizador MATERIALIZE e INLINE se pueden utilizar para influir en la decisión. La sugerencia MATERIALIZE no documentada le dice al optimizador que resuelva la subconsulta como una tabla temporal global, mientras que la sugerencia INLINE le dice que procese la consulta en línea.

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

Incluso cuando no hay repetición de SQL, la cláusula WITH puede simplificar consultas complejas, como el siguiente ejemplo que enumera los departamentos con salarios superiores al promedio.

WITH 
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
    GROUP BY dname),
  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 dname;

En el ejemplo anterior, el cuerpo principal de la consulta es muy simple, con la complejidad oculta en la cláusula WITH.

MATERIALIZE Hint

La sugerencia indocumentada MATERIALIZE se mencionó anteriormente, pero parece haber un poco de confusión sobre cómo se implementa. Podemos ver lo que está sucediendo bajo las sábanas usando el rastreo de SQL.
Crea una tabla de prueba.

CONN test/test

CREATE TABLE t1 AS
SELECT level AS id
FROM   dual
CONNECT BY level <= 100; Verifique la ubicación del archivo de seguimiento. SELECT value FROM v$diag_info WHERE name = 'Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_4278.trc SQL>

Rastree una declaración usando MATERIALIZE Hint.

EXEC DBMS_MONITOR.session_trace_enable;

WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t1
)
SELECT * FROM query1;

EXEC DBMS_MONITOR.session_trace_disable;

La siguiente salida abreviada muestra algunos puntos de interés en el archivo de seguimiento resultante. Observe las líneas «CREATE GLOBAL TEMPORARY T» y «TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB». Ciertamente, parece estar usando una tabla temporal global.

=====================
PARSING IN CURSOR #140100560521424 len=174 dep=1 uid=0 oct=1 lid=0 tim=733844612 hv=1878591410 ad='80b179f0' sqlid='40a2untrzk1xk'
CREATE GLOBAL TEMPORARY T
END OF STMT
...
=====================
...
=====================
PARSING IN CURSOR #140100560423976 len=77 dep=0 uid=109 oct=3 lid=109 tim=733865863 hv=3518560624 ad='a35bc6c0' sqlid='9fzhbw78vjybh'
WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t1
)
SELECT * FROM query1
END OF STMT
...
STAT #140100560423976 id=1 cnt=100 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION  (cr=15 pr=1 pw=1 time=19589 us)'
STAT #140100560423976 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT  (cr=3 pr=0 pw=1 time=16243 us)'
STAT #140100560423976 id=3 cnt=100 pid=2 pos=1 obj=91676 op='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=1514 us cost=3 size=300 card=100)'
STAT #140100560423976 id=4 cnt=100 pid=1 pos=2 obj=0 op='VIEW  (cr=12 pr=1 pw=0 time=1257 us cost=2 size=1300 card=100)'
STAT #140100560423976 id=5 cnt=100 pid=4 pos=1 obj=4254950955 op='TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB (cr=12 pr=1 pw=0 time=1203 us cost=2 size=300 card=100)'
...
=====================

Esta es una característica indocumentada. Le he dado un ejemplo que usa una tabla temporal global, pero tal vez haya otras circunstancias que no lo hagan.

PL/SQL Declaration Section

Una mejora de Oracle Database 12c permite declaraciones PL/SQL en la cláusula WITH.

Quizás también te puedas interesar:

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *