Tengo un gran problema con una sentencia SQL en Oracle. Quiero seleccionar los 10 primeros registros ordenados por STORAGE_DB que no están en una lista de otra sentencia select.
Esta funciona bien para todos los registros:
SELECT DISTINCT
APP_ID,
NAME,
STORAGE_GB,
HISTORY_CREATED,
TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE
FROM HISTORY WHERE
STORAGE_GB IS NOT NULL AND
APP_ID NOT IN (SELECT APP_ID
FROM HISTORY
WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')
Pero cuando añado
AND ROWNUM <= 10
ORDER BY STORAGE_GB DESC
I'm obtener algún tipo de "al azar" Registros. Creo que porque el límite toma en su lugar antes de la orden.
¿Alguien tiene una buena solución? El otro problema: Esta consulta es realmente lento (10k + registros)
Tendrá que poner su consulta actual en la subconsulta como se indica a continuación:
SELECT * FROM (
SELECT DISTINCT
APP_ID,
NAME,
STORAGE_GB,
HISTORY_CREATED,
TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE
FROM HISTORY WHERE
STORAGE_GB IS NOT NULL AND
APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
ORDER BY STORAGE_GB DESC )
WHERE ROWNUM <= 10
Oracle aplica rownum al resultado después de haberlo devuelto.
Necesita filtrar el resultado después de haberlo devuelto, por lo que se requiere una subconsulta. También puede utilizar la función RANK() para obtener los resultados Top-N.
Para mejorar el rendimiento, intente utilizar NOT EXISTS
en lugar de NOT IN
. Vea this para más información.
En lo que respecta al bajo rendimiento, hay muchas cosas que podrían ser, y realmente debería ser una cuestión aparte. Sin embargo, hay una cosa obvia que podría ser un problema:
WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')
Si HISTORY_DATE es realmente una columna de fecha y si tiene un índice, entonces esta reescritura tendrá un mejor rendimiento:
WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')
Esto se debe a que una conversión de tipo de datos desactiva el uso de un índice B-Tree.
Se obtiene un conjunto aparentemente aleatorio porque ROWNUM se aplica antes de ORDER BY. Así que su consulta toma las diez primeras filas y las ordena.0 Para seleccionar los diez salarios más altos debe utilizar una función analítica en una subconsulta, y luego filtrarla:
select * from
(select empno,
ename,
sal,
row_number() over(order by sal desc nulls last) rnm
from emp)
where rnm<=10