Is er een manier om een oracle
-query te laten werken alsof deze een MySQL LIMIT
-clausule bevat?
In MySQL kan ik dit doen:
select *
from sometable
order by name
limit 20,10
om de 21e tot de 30e rij te krijgen (sla de eerste 20 over, geef de volgende 10). De rijen worden geselecteerd na de order by
, dus het begint echt alfabetisch op de 20e naam.
In Oracle is het enige dat mensen vermelden de ROWNUM
pseudo-kolom, maar deze wordt voordatorder by
geëvalueerd, wat betekent dat dit :
select *
from sometable
where rownum <= 10
order by name
zal een willekeurige set van tien rijen retourneren, geordend op naam, wat meestal niet is wat ik wil. Het staat ook niet toe om een offset op te geven.
Antwoord 1, autoriteit 100%
Vanaf Oracle 12c R1 (12.1) is er iseen regelbeperking voor rijen. Het gebruikt geen bekende LIMIT
-syntaxis, maar het kan het werk beter doen met meer opties. Je kunt de volledige syntaxis hiervinden. (Lees ook meer over hoe dit intern in Oracle werkt in dit antwoord).
Om de oorspronkelijke vraag te beantwoorden, hier is de vraag:
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
(voor eerdere Oracle-versies, raadpleeg dan andere antwoorden in deze vraag)
Voorbeelden:
De volgende voorbeelden zijn geciteerd van Gelinkte pagina , in de hoop Link Rot te voorkomen.
SETUP
CREATE TABLE rownum_order_test (
val NUMBER
);
INSERT ALL
INTO rownum_order_test
SELECT level
FROM dual
CONNECT BY level <= 10;
COMMIT;
Wat zit er in de tafel?
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.
Verkrijg de eerste N
rijen
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;
VAL
----------
10
10
9
9
8
5 rows selected.
Verkrijg de eerste N
Rijen, als N
TH sup>Row banden heeft, ontvang alle gebonden rijen
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
% van de rijen
SELECT val
FROM rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;
VAL
----------
1
1
2
2
4 rows selected.
Met behulp van een offset, zeer nuttig zijn voor de paginering
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.
U kunt combineren compenseren met percentages
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.
2, Autoriteit 108%
U kunt een subquery gebruiken voor dit zoals
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Heeft ook een kijkje nemen op het onderwerp On ROWNUM en het beperken van de resultaten bij Oracle / AskTom voor meer informatie.
Bijwerken :
Om het resultaat te beperken met zowel onder- en bovengrens dingen een beetje opgeblazen met
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;
(gekopieerd gespecificeerd AskTom voorwerpdragende)
Update 2 :
Te beginnen met Oracle 12c (12.1) is er een syntax beschikbaar voor limiet rijen of beginnen bij offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Zie dit antwoordvoor meer voorbeelden. Met dank aan Krumia voor de hint.
Antwoord 3, autoriteit 25%
Ik heb prestatietests gedaan voor de volgende benaderingen:
Vraag
select * from (
select a.*, ROWNUM rnum from (
<select statement with order by clause>
) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW
Analytisch
select * from (
<select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW
Kort alternatief
select * from (
select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW
Resultaten
Tabel had 10 miljoen records, sortering was op een niet-geïndexeerde datetime-rij:
- Leg plan toonde dezelfde waarde voor alle drie de selecties (323168)
- Maar de winnaar is AskTom (met analytische volgers vlak achter)
Het selecteren van de eerste 10 rijen duurde:
- AskTom: 28-30 seconden
- Analytisch: 33-37 seconden
- Kort alternatief: 110-140 seconden
Rijen tussen 100.000 en 100.010 selecteren:
- AskTom: 60 seconden
- Analytisch: 100 seconden
Rijen tussen 9.000.000 en 9.000.010 selecteren:
- AskTom: 130 seconden
- Analytisch: 150 seconden
Antwoord 4, autoriteit 8%
Een analytische oplossing met slechts één geneste zoekopdracht:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()
kan worden vervangen door Row_Number()
maar kan meer records retourneren dan u verwacht als er dubbele waarden zijn voor naam.
Antwoord 5, autoriteit 4%
Op Oracle 12c (zie rijbeperkingsclausule in SQL-referentie):
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Antwoord 6, autoriteit 3%
SQL-standaard
Sinds versie 12c ondersteunt Oracle de SQL:2008-standaard, die de volgende syntaxis biedt om de SQL-resultatenset te beperken:
SELECT
title
FROM
post
ORDER BY
id DESC
FETCH FIRST 50 ROWS ONLY
Oracle 11g en oudere versies
Vóór versie 12c, om de Top-Nop te halen records, moest je een afgeleide tabel gebruiken en de ROWNUM
pseudokolom:
SELECT *
FROM (
SELECT
title
FROM
post
ORDER BY
id DESC
)
WHERE ROWNUM <= 50
Antwoord 7, autoriteit 2%
Pagineringsquery’s met volgorde zijn erg lastig in Oracle.
Oracle biedt een ROWNUM-pseudokolom die een getal retourneert dat de volgorde aangeeft waarin de database de rij selecteert uit een tabel of een reeks samengevoegde weergaven.
ROWNUM is een pseudokolom die veel mensen in de problemen brengt. Een ROWNUM-waarde wordt niet permanent toegewezen aan een rij (dit is een veelvoorkomend misverstand). Het kan verwarrend zijn wanneer een ROWNUM-waarde daadwerkelijk wordt toegewezen. Een ROWNUM-waarde wordt toegewezen aan een rij nadat deze de filterpredikaten heeft doorstaanvan de zoekopdracht, maar voordat de zoekopdracht wordt geaggregeerd of gesorteerd.
Bovendien wordt een ROWNUM-waarde pas verhoogd nadat deze is toegewezen.
Dit is de reden waarom de volgende zoekopdracht geen rijen retourneert:
select *
from (select *
from some_table
order by some_column)
where ROWNUM <= 4 and ROWNUM > 1;
De eerste rij van het zoekresultaat komt niet door ROWNUM > 1 predikaat, dus ROWNUM wordt niet verhoogd naar 2. Om deze reden wordt geen enkele ROWNUM-waarde groter dan 1, en daarom retourneert de query geen rijen.
Een correct gedefinieerde zoekopdracht zou er als volgt uit moeten zien:
select *
from (select *, ROWNUM rnum
from (select *
from skijump_results
order by points)
where ROWNUM <= 4)
where rnum > 1;
Lees meer over paginatiequery’s in mijn artikelen op Vertabeloblog:
8
minder aangegeven uitspraken. Ook minder prestaties consumeren. Credits to: [email protected]
SELECT *
FROM (SELECT t.*,
rownum AS rn
FROM shhospede t) a
WHERE a.rn >= in_first
AND a.rn <= in_first;
9
Als u niet op Oracle 12C bent, kunt u TOP N-query zoals hieronder gebruiken.
SELECT *
FROM
( SELECT rownum rnum
, a.*
FROM sometable a
ORDER BY name
)
WHERE rnum BETWEEN 10 AND 20;
U kunt dit zelfs vanuit clausule in met clausule als volgt verplaatsen
WITH b AS
( SELECT rownum rnum
, a.*
FROM sometable a ORDER BY name
)
SELECT * FROM b
WHERE rnum BETWEEN 10 AND 20;
Hier maken we eigenlijk een inline-weergave en hernoemen we rownum naar rnum. U kunt rnum in de hoofdquery gebruiken als filtercriteria.
Antwoord 10
Ik ben begonnen met de voorbereiding op het Oracle 1z0-047-examen, gevalideerd tegen 12c
Terwijl ik me erop voorbereidde, kwam ik een 12c-verbetering tegen die bekend staat als ‘FETCH FIRST’
Hiermee kunt u rijen ophalen / rijen beperken volgens uw gemak.
Er zijn verschillende opties mee beschikbaar
- 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
Voorbeeld:
Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY
Antwoord 11
Voor elke rij die door een query wordt geretourneerd, retourneert de ROWNUM-pseudokolom een getal dat de volgorde aangeeft waarin Oracle de rij selecteert uit een tabel of een reeks samengevoegde rijen. De eerste geselecteerde rij heeft een ROWNUM van 1, de tweede heeft 2, enzovoort.
SELECT * FROM sometable1 so
WHERE so.id IN (
SELECT so2.id from sometable2 so2
WHERE ROWNUM <=5
)
AND ORDER BY so.somefield AND ROWNUM <= 100
Ik heb dit geïmplementeerd in oracle
server 11.2.0.1.0
Antwoord 12
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5
groter dan waarden, ontdek
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID>5
minder dan waarden ontdekken
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5
Antwoord 13
(niet getest) zoiets als dit kan het werk doen
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
Er is ook de rangorde van de analytische functie, die u kunt gebruiken om te ordenen.