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)
Les exemples suivants ont été cités à partir d’une page liée , dans l’espoir d’empêcher la pourriture des liens.
CREATE TABLE rownum_order_test ( val NUMBER ); INSERT ALL INTO rownum_order_test SELECT level FROM dual CONNECT BY level <= 10; COMMIT;
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.
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.
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.
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.
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.
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:
select * from ( select a.*, ROWNUM rnum from (
select * from (
select * from ( select statement, rownum as RN with order by clause ) where a.rn >= MIN_ROW and a.rn <= MAX_ROW
Table avait 10 millions d'enregistrements, le sorting était sur une ligne datex non indexée:
La sélection des 10 premières lignes a pris:
Sélection des lignes entre 100 000 et 100 010:
Sélection des lignes entre 9 000 000 et 9 000 010:
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 :
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;
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.