Oracle SELECT TOP 10 enregistrements

Question

J'ai un gros problème avec une déclaration SQL dans Oracle. Je veux sélectionner les 10 premiers enregistrements classés par STORAGE_DB qui ne sont pas dans une liste d'une autre instruction select.

Celui-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 quand j'ajoute

AND ROWNUM <= 10
ORDER BY STORAGE_GB DESC

Je reçois une sorte d'enregistrement "aléatoire". 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).

Answer 1

Vous devrez mettre 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 au résultat après qu'il ait été retourné.
Vous devez filtrer le résultat après qu'il a été renvoyé, ce qui nécessite une sous-requête. Vous pouvez également utiliser RANK() pour obtenir des résultats Top-N.

Pour améliorer les performances, essayez d'utiliser NOT EXISTS à la place de NOT IN . Voir ce pour plus.

Answer 2

Si vous utilisez Oracle 12c, utilisez :

FETCH NEXT N ROWS ONLY

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
FETCH NEXT 10 ROWS ONLY

Plus d'informations : http://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html

Answer 3

Essayez

SELECT * FROM users FETCH NEXT 10 ROWS ONLY;

Answer 4

En ce qui concerne les mauvaises performances, il y a un grand nombre de choses qui peuvent être à l'origine de cette situation, 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 possède un index, cette réécriture sera plus performante :

WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')  

Cela est dû au fait qu'une conversion de type de données désactive l'utilisation d'un index B-Tree.

Answer 5

Vous obtenez un ensemble apparemment aléatoire parce que ROWNUM est appliqué avant le 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