Hoe beperk ik het aantal rijen dat wordt geretourneerd door een Oracle-query na het bestellen?

January 18, 2022

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 ROWNUMpseudo-kolom, maar deze wordt voordatorder bygeë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 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 ROWNUMpseudokolom:

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

Previous articleXCOPY: alles overschrijven zonder prompt in BATCH

Next articleHoe krijg ik de huidige datum en tijd in PHP?