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

Est-il possible de faire en sorte qu’une requête Oracle se comporte comme si elle contenait une clause MySQL limit ?

En MySQL , je peux le faire:

 select * from sometable order by name limit 20,10 

pour obtenir la 21ème à la 30ème rangée (sautez les 20 premières, donnez les 10 suivantes). Les lignes sont sélectionnées après la order by , elle commence donc par le 20ème nom par ordre alphabétique.

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

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

renverra un ensemble aléatoire de dix lignes 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.

À partir d’Oracle 12c R1 (12.1), il existe une clause limitant les lignes . Il n’utilise pas la syntaxe LIMIT familière, mais il peut mieux faire le travail avec plus d’options. Vous pouvez trouver la 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 reporter aux autres réponses à cette question)


Exemples:

Les exemples suivants ont été cités à partir d’une 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'est-ce qu'il y a dans la table?

 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 les N premières lignes

 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 les N premières lignes, si N ème ligne a des liens, obtenez 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. 

Top 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. 

Utiliser 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. 

Vous pouvez utiliser une sous-requête comme ceci

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

Consultez également le sujet On ROWNUM et limitez les résultats à Oracle / AskTom pour plus d'informations.

Mise à 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 (  ) 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 démarrer les 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 cet indice.

J’ai fait des tests de performance pour les approches suivantes:

Asktom

 select * from ( select a.*, ROWNUM rnum from (  

Analytique

 select * from (  

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

Table avait 10 millions d'enregistrements, le sorting était sur une ligne datex non indexée:

  • Expliquez que le plan a montré la même valeur pour les trois sélections (323168)
  • Mais le gagnant est AskTom (avec une parsing qui suit de près)

La sélection des 10 premières lignes 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

Une solution analytique avec une seule requête nestede:

 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 pourrait renvoyer plus d’enregistrements que Row_Number() s’il existe des valeurs en double pour le nom.

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

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

Les requêtes de pagination avec classement sont vraiment délicates dans Oracle.

Oracle fournit une pseudo-colonne ROWNUM qui renvoie un nombre indiquant l’ordre dans lequel la firebase database sélectionne la ligne d’une table ou d’un ensemble de vues jointes.

ROWNUM est une pseudo-colonne qui cause des problèmes à de nombreuses personnes. Une valeur ROWNUM n’est pas affectée de manière permanente à une ligne (il s’agit d’un malentendu courant). Cela peut être déroutant lorsqu’une valeur ROWNUM est effectivement atsortingbuée. Une valeur ROWNUM est affectée à une ligne après avoir passé les prédicats de filtre de la requête, mais avant l’agrégation ou le sorting des requêtes .

De plus, une valeur ROWNUM est incrémentée uniquement après son affectation.

C’est pourquoi la requête followin 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 passe pas le prédicat ROWNUM> 1, donc ROWNUM ne s’incrémente pas à 2. Pour cette raison, aucune valeur ROWNUM ne dépasse 1, par conséquent, la requête ne renvoie aucune ligne.

Une 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 le blog Vertabelo :

  • Oracle ROWNUM expliqué
  • Requêtes Top-N et pagination

Moins d’instructions SELECT. Aussi, moins de performance. Crédits à: [email protected]

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

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 cette clause de l’article avec la clause suivante

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

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

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

plus grand alors les valeurs découvrent

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

moins que les valeurs découvrir

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

J’ai commencé à préparer l’examen Oracle 1z0-047, validé par rapport à 12c. En préparant cela, je suis tombé sur une amélioration 12c appelée «FETCH FIRST» qui vous permet de récupérer des lignes / limiter les lignes selon votre convenance. Plusieurs options sont disponibles avec elle

 - FETCH FIRST n ROWS ONLY - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows - n % rows via FETCH FIRST N PERCENT ROWS ONLY 

Exemple:

 Select * from XYZ a order by a.pqr FETCH FIRST 10 ROWS ONLY 

En oracle

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

VAL

  10 10 9 9 8 

5 lignes sélectionnées.

SQL>

(non testé) quelque chose comme ça peut faire le travail

 WITH base AS ( select * -- get the table from sometable order by name -- in the desired order ), twenty AS ( select * -- get the first 30 rows from base where rownum < 30 order by name -- in the desired order ) select * -- then get rows 21 .. 30 from twenty where rownum > 20 order by name -- in the desired order 

Il y a aussi le rang de la fonction analytique, que vous pouvez utiliser pour passer commande.

Comme ci-dessus avec des corrections. Fonctionne mais certainement pas joli.

  WITH base AS ( select * -- get the table from sometable order by name -- in the desired order ), twenty AS ( select * -- get the first 30 rows from base where rownum <= 30 order by name -- in the desired order ) select * -- then get rows 21 .. 30 from twenty where rownum < 20 order by name -- in the desired order 

Honnêtement, mieux vaut utiliser les réponses ci-dessus.