Question Comment puis-je limiter le nombre de lignes renvoyées par une requête Oracle après la commande?


Y a-t-il un moyen de faire un Oracle requête se comporte comme il contient un MySQL limit clause?

Dans MySQL, Je peux le faire:

select * 
from sometable
order by name
limit 20,10

pour obtenir le 21ème aux 30ème rangs (sautez les 20 premiers, donnez les 10 suivants). Les lignes sont sélectionnées après le order by, donc ça commence vraiment au 20ème nom par ordre alphabétique.

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

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

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


805
2018-01-22 19:48


origine


Réponses:


À partir d'Oracle 12c R1 (12.1), il est une clause de limitation de ligne. Il n'utilise pas familier LIMIT syntaxe, mais il peut mieux faire le travail avec plus d'options. Vous pouvez trouver le syntaxe complète ici.

Pour répondre à la question initiale, voici la requête:

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

(Pour les versions antérieures d'Oracle, veuillez vous référer aux autres réponses dans cette question)


Exemples:

Les exemples suivants ont été cités page liée, dans l'espoir d'empêcher la pourriture des liens.

Installer

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

Qu'y a-t-il dans le tableau?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

Obtenez d'abord N rangées

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Obtenez d'abord N rangées, si Nth rangée a des liens, obtenir toutes les lignes liées

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

Haut x% de lignes

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

En utilisant un offset, très utile pour la pagination

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Vous pouvez combiner l'offset avec des pourcentages

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

328
2017-09-26 04:01



Vous pouvez utiliser une sous-requête pour cela comme

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

Jetez aussi un coup d'oeil sur le sujet Sur ROWNUM et limiter les résultats à Oracle / AskTom pour plus d'informations.

Mettre à jour: Pour limiter le résultat avec les 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é à partir de l'article AskTom spécifié)

Mise à jour 2: À partir d'Oracle 12c (12.1), une syntaxe est disponible pour limiter les lignes ou commencer à des décalages.

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

Voir cette réponse pour plus d'exemples. Merci à Krumia pour l'indice.


713
2018-01-22 19:55



J'ai fait 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 comptait 10 millions d'enregistrements, le tri était sur une ligne datetime non indexée:

  • Expliquer le plan a montré la même valeur pour les trois sélections (323168)
  • Mais le gagnant est AskTom (suivi analytique derrière lui)

La sélection des 10 premières lignes a pris:

  • AskTom: 28-30 secondes
  • Analytique: 33-37 secondes
  • Courte alternative: 110-140 secondes

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

  • AskTom: 60 secondes
  • Analytique: 100 secondes

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

  • AskTom: 130 secondes
  • Analytique: 150 secondes

166
2018-06-30 14:20



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 substitué à Row_Number() mais peut renvoyer plus d'enregistrements que ce que vous attendez s'il existe des valeurs en double pour le nom.


52
2018-01-23 14:28



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

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

27
2017-09-24 03:09



Les requêtes de pagination avec la commande sont vraiment compliquées dans Oracle.

Oracle fournit une pseudocolonne ROWNUM qui renvoie un nombre indiquant l'ordre dans lequel la base de données sélectionne la ligne d'une table ou d'un ensemble de vues jointes.

ROWNUM est une pseudo-colonne qui provoque beaucoup de problèmes. Une valeur ROWNUM n'est pas affectée de façon permanente à une ligne (c'est un malentendu courant). Cela peut être déroutant lorsqu'une valeur ROWNUM est réellement assignée. Une valeur ROWNUM est affectée à une ligne après qu'il passe les prédicats de filtre de la requête, mais avant l'agrégation de la requête ou le tri.

De plus, une valeur ROWNUM n'est incrémentée qu'après avoir été affectée.

C'est pourquoi la requête suivante ne renvoie aucune ligne:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

La première ligne du résultat de la requête ne transmet pas le prédicat ROWNUM> 1, donc ROWNUM n'incrémente pas à 2. Pour cette raison, aucune valeur ROWNUM ne dépasse 1, par conséquent, la requête ne renvoie aucune ligne.

La requête correctement définie doit ressembler à ceci:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

En savoir plus sur les requêtes de pagination dans mes articles sur Vertabelo Blog:


10
2018-04-12 17:32



Moins d'instructions SELECT. En outre, moins de performances consommatrices. 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;

7
2018-03-02 14:32



Si vous n'êtes pas sur Oracle 12C, vous pouvez utiliser la requête TOP N comme ci-dessous.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

Vous pouvez même déplacer ceci de la clause in avec clause comme suit

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

Ici, nous créons une vue en ligne et renommons rownum en tant que rnum. Vous pouvez utiliser rnum dans la requête principale comme critère de filtre.


3
2017-12-08 10:11



select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

plus grandes que les valeurs découvrent

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

moins alors les valeurs découvrent

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

3
2018-04-12 11:01