J'ai un gros problème avec une instruction SQL dans Oracle. Je veux sélectionner les 10 premiers enregistrements classés par STORAGE_DB qui ne figurent pas dans une liste provenant d'une autre instruction select.
Celle-ci fonctionne bien pour tous les enregistrements :
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')
Mais lorsque j'ajoute
AND ROWNUM <= 10
ORDER BY STORAGE_GB DESC
j'obtiens une sorte de "enregistrements aléatoires" ;. Je pense que c'est parce que la limite se met en place avant l'ordre.
Quelqu'un a-t-il une bonne solution ? L'autre problème : cette requête est très lente (plus de 10 000 enregistrements).
Vous devez placer votre requête actuelle dans une sous-requête comme ci-dessous :
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 applique [rownum][1] au résultat après qu'il ait été retourné. Vous devez filtrer le résultat après qu'il ait été retourné, donc une sous-requête est nécessaire. Vous pouvez également utiliser la fonction [RANK()][2] pour obtenir les résultats les plus importants.
Pour améliorer les performances, essayez d'utiliser NOT EXISTS
à la place de NOT IN
. Voir [this][3] pour en savoir plus.
[1] : http://www.adp-gmbh.ch/ora/sql/rownum.html [2] : http://www.adp-gmbh.ch/ora/sql/analytical/rank.html [3] : http://www.4tm.com.ar/4tmsite/wordpress/?p=25
En ce qui concerne les mauvaises performances, il y a un certain nombre de choses qui peuvent être à l'origine de ce problème, et cela devrait vraiment faire l'objet d'une question distincte. Cependant, il y a une chose évidente qui pourrait être un problème :
WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')
Si HISTORY_DATE est vraiment une colonne de date et si elle a un index, alors cette réécriture sera plus performante :
WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')
Ceci est dû au fait qu'une conversion de type de données désactive l'utilisation d'un index B-Tree.
Vous obtenez un ensemble apparemment aléatoire parce que ROWNUM est appliqué avant ORDER BY. Pour sélectionner les dix premiers salaires, vous devez utiliser une fonction analytique dans une sous-requête, puis filtrer celle-ci :
select * from
(select empno,
ename,
sal,
row_number() over(order by sal desc nulls last) rnm
from emp)
where rnm<=10