Cláusula QUALITY
Veamos como podemos solucionar un problema simple del día a día.
Definición del problema:
Necesitamos conocer, cuál es el empleado mejor pagado en cada departamento.?
Tomemos para este ejemplo la clásica tabla del esquema de ejemplos HR.EMPLOYEES.
Tradicionalmente en Oracle (hasta 19c/21c) este problema se podría haber solucionado de esta manera:
SELECT *FROM (
SELECT employee_id,department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
FROM hr.employees
)
WHERE rn = 1;
EMPLOYEE_ID DEPARTMENT_ID RN
----------- ------------- ----------
200 10 1
201 20 1
114 30 1
203 40 1
121 50 1
103 60 1
204 70 1
145 80 1
100 90 1
108 100 1
205 110 1
178 1
12 rows selected.
Explicación de la función analítica ROW_NUMBER()
PARTITION BY department_id ==> divide el conjunto de datos en grupos por departamento
ORDER BY salary DESC ==> ordena los empleados dentro de cada departamento desde el salario mayor al menor
ROW_NUMBER() ==> asigna una numeración secuencial empezando en 1 para cada grupo
Desde Oracle AI Database 26ai, existe una nueva cláusula que resulta especialmente relevante desde la perspectiva del lenguaje de programación SQL/PLSQL y el modelo de programción: QUALIFY (filtrado post-analítico sin subquery), que permite simplificar el filtrado de resultados de funciones analíticas (window functions) sin necesidad de realizar una subconsulta.
La consulta inicial se puede expresar directamente en 26ai de la siguiente manera:
SELECT employee_id,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
FROM hr.employees
QUALIFY rn = 1;
EMPLOYEE_ID DEPARTMENT_ID RN
----------- ------------- ----------
200 10 1
201 20 1
114 30 1
203 40 1
121 50 1
103 60 1
204 70 1
145 80 1
100 90 1
108 100 1
205 110 1
178 1
12 rows selected.
La cláusula QUALIFY filtra el resultado después de calcular la función analítica.
Relevancia técnica
Desde la perspectiva del optimizador:
- elimina subqueries artificiales
- mejora legibilidad declarativa
- facilita generación automática de SQL por LLMs
- simplifica pipelines de analytics y feature engineering
Oracle posiciona QUALIFY como el equivalente analítico de HAVING, pero aplicado a funciones window.
Este tipo de mejora suele pasar desapercibida frente a AI Vector Search, pero impacta directamente el diseño de consultas complejas, especialmente en:
scoring de fraude
ranking de embeddings
top-K similarity search
deduplicación basada en ventanas
A nivel del plan de ejecución podemos ver que ambas consultas a pesar de brindar el mismo resultado, no son equivalentes de cara al optimizador, aunque a nivel de costo, son equivalentes.
[oracle@oracle-server-26ai ~]$ sqlplus /nolog
SQL*Plus: Release 23.26.1.0.0 - Production on Mon Apr 6 02:50:48 2026
Version 23.26.1.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
SQL> connect einsten/oracle@pdb1
Connected.
SQL> set linesize 200
SQL> set pagesize 20
SQL> explain plan for
2 SELECT *
FROM (
SELECT employee_id,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
FROM hr.employees
)
WHERE rn = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2698234872
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 220 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 11 | 220 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 107 | 1177 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPARTMENT_ID" ORDER BY
"SALARY" DESC )<=1)
17 rows selected.
SQL> explain plan for
2 SELECT employee_id,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
FROM hr.employees
QUALIFY rn = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2698234872
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 242 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 11 | 242 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 107 | 1177 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."sys_qualify$_h$_predex$_0"=TRUE)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPARTMENT_ID" ORDER BY
"SALARY" DESC )<=1)
17 rows selected.
SQL>
Si observamos detenidamente, veremos que con el uso de la cláusula "QUALITY" el optimizador realiza un filtro en la creación de la vista con "filter("from$_subquery$_002"."sys_qualify$_h$_predex$_0"=TRUE)
- from$_subquery$_002: Es un alias interno generado por Oracle para una subconsulta (inline view) que el optimizador ha creado o transformado para procesar la lógica de tu SQL.
- sys_qualify: Se refiere a la operación de filtrado sobre funciones de ventana. En versiones recientes de Oracle (como la 23ai), se introdujo formalmente la cláusula QUALIFY de Oracle, que permite filtrar directamente por funciones analíticas.
- _h$_predex$_0=TRUE: Es un predicado interno de "pre-ejecución" (predex). Indica que el optimizador ha determinado una condición que debe cumplirse (ser TRUE) para que las filas pasen a la siguiente etapa del plan.
Aquí lo importante de tener en cuenta, es la eliminación de la subconsulta y la simplificación de la misma.
No hay comentarios:
Publicar un comentario
Te agradezco tus comentarios. Te esperamos de vuelta.