Comment limiter le nombre de lignes renvoyées par une requête Oracle après avoir été ordonnée ?

Question

Existe-t-il un moyen de faire un Oracle se comporte comme si elle contenait une requête MySQL limit clause ?

Dans MySQL, je peux faire cela :

select * 
from sometable
order by name
limit 20,10

pour obtenir les 21e à 30e rangs (sautez les 20 premiers, donnez les 10 suivants). Les rangs sont sélectionnés après le order by Il faut donc commencer par le 20ème nom par ordre alphabétique.

Dans Oracle, la seule chose que les gens mentionnent est la rownum pseudo-colonne, mais elle est évaluée avant order by ce qui signifie ceci :

select * 
from sometable
where rownum <= 10
order by name

retournera un ensemble aléatoire de dix lignes classées par nom, ce qui n'est généralement pas ce que je veux. Elle ne permet pas non plus de spécifier un décalage.

Answer 1

Vous pouvez utiliser une sous-requête pour cela, par exemple

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Jetez également un coup d'œil à la rubrique Sur le ROWNUM et la limitation des résultats à Oracle/AskTom pour plus d'informations.

Mise à jour : Pour limiter le résultat avec des limites inférieures et supérieures, les choses deviennent un peu plus gonflées avec

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copié de l'article spécifié AskTom-article)

Mise à jour 2 : À partir d'Oracle 12c (12.1), il existe une syntaxe permettant de limiter les lignes ou de commencer à des décalages.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Ver cette réponse pour plus d'exemples. Merci à Krumia pour l'astuce.

Answer 2

J'ai effectué quelques tests de performance pour les approches suivantes :

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Analytique

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

Alternative courte

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Résultats

La table contient 10 millions d'enregistrements, le tri se fait sur une ligne de date non indexée :

• Expliquer le plan a montré la même valeur pour les trois sélections (323168)
• Mais le gagnant est AskTom (suivi de près par Analytic).

La sélection des 10 premiers rangs a pris :

• AskTom : 28-30 secondes
• Analytique : 33-37 secondes
• Alternative courte : 110-140 secondes

Sélection des lignes entre 100 000 et 100 010 :

• AskTom : 60 secondes
• Analytique : 100 secondes

Sélection des lignes entre 9 000 000 et 9 000 010 :

• AskTom : 130 secondes
• Analytique : 150 secondes

Answer 3

Une solution analytique avec une seule requête imbriquée :

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() pourrait être remplacé par Row_Number() mais peut renvoyer plus d'enregistrements que ce que vous attendez s'il y a des valeurs en double pour le nom.

Answer 4

Sur Oracle 12c (voir la clause de limitation de rangée dans Référence SQL ) :

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Answer 5

Moins d'instructions SELECT. Aussi, moins de consommation de performance. Crédits : anibal@upf.br

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;