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 en sorte qu'une requête Oracle se comporte comme si elle contenait une clause MySQL limit ?
Dans MySQL , je peux le faire:
select *
from sometable
order by name
limit 20,10
pour passer du 21ème au 30ème rang (sauter les 20 premiers, donner les 10 suivants). Les lignes sont sélectionnées après le order by , donc il commence vraiment sur le 20ème nom par ordre alphabétique.
Dans Oracle , la seule chose que les gens de mentionner le rownum pseudo-colonne, mais il est évalué avant order by , ce qui signifie:
select *
from sometable
where rownum <= 10
order by name
retournera un ensemble aléatoire de dix lignes ordonnées par nom, ce qui n'est généralement pas ce que je veux. Il ne permet pas non plus de spécifier un offset.
4 réponses
à partir de Oracle 12c R1 (12.1), il est a row limiting clause . Il n'utilise pas familier LIMIT la syntaxe, mais il peut faire le travail mieux avec plus d'options. Vous pouvez trouver la syntaxe complète ici .
pour répondre à la question originale, voici la requête:
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
(pour les versions précédentes D'Oracle, veuillez vous reporter aux autres réponses à cette question))
exemples:
les exemples suivants ont été cités à partir de page liée , dans l'espoir de prévenir la pourriture du lien.
Setup
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.
d'abord N 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.
d'abord N de lignes, si N th de ligne, qui a des liens, obtenir toutes à égalité les lignes
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 % des lignes
SELECT val
FROM rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;
VAL
----------
1
1
2
2
4 rows selected.
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.
vous pouvez utiliser un subquery pour ce comme
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Ont également un oeil à la rubrique Sur ROWNUM et de limiter les résultats chez Oracle/AskTom pour plus d'informations.
mise à Jour : Pour limiter le résultat avec les deux limites inférieures et supérieures choses obtenir un peu plus gonflé 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 specified AskTom-article)
mise à Jour 2 : En commençant par Oracle 12c (12.1) il y a une syntaxe disponible pour limiter les lignes ou commencer à offsets.
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 le tuyau.
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
Short Alternative
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, sort était sur une ligne de date-heure non indexée:
- expliquer plan montré la même valeur pour les trois sélections (323168)
- Mais le gagnant est AskTom (avec analytiques qui s'en suivent)
sélection des 10 premières lignes a pris:
- AskTom: 28-30 secondes
- analytique: 33-37 secondes
- alternative courte: 110-140 secondes
sélection de lignes entre 100 000 et 100 010:
- AskTom: 60 seconds
- analytique: 100 secondes
sélection de lignes entre 9 000 000 et 9 000 010:
- AskTom: 130 secondes
- analytique: 150 secondes
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 pourrait retourner plus d'enregistrements que vous attendez s'il y a des valeurs dupliquées pour le nom.