- 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.