Frage Wie beschränke ich die Anzahl der Zeilen, die nach der Bestellung von einer Oracle-Abfrage zurückgegeben werden?


Gibt es einen Weg, ein zu machen? Oracle Abfrage verhalten sich wie es enthält a MySQL limit Klausel?

Im MySQL, Ich kann dies tun:

select * 
from sometable
order by name
limit 20,10

um die 21. bis 30. Reihe zu bekommen (die ersten 20 überspringen, die nächsten 10 geben). Die Zeilen sind nach dem ausgewählt order by, so fängt es wirklich am 20. alphabetisch an.

Im OracleDas einzige, was die Leute erwähnen, ist das rownum Pseudo-Spalte, aber es wird ausgewertet Vor  order by, was das bedeutet:

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

wird eine zufällige Menge von zehn Zeilen nach Namen geordnet zurückgeben, was normalerweise nicht das ist, was ich möchte. Es kann auch kein Offset angegeben werden.


805
2018-01-22 19:48


Ursprung


Antworten:


Ausgehend von Oracle 12c R1 (12.1), dort ist ein Zeilenbegrenzungsklausel. Es verwendet nicht vertraut LIMIT Syntax, aber es kann den Job mit mehr Optionen besser machen. Sie können die finden vollständige Syntax hier.

Um die ursprüngliche Frage zu beantworten, hier ist die Abfrage:

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

(Für frühere Oracle Versionen, beziehen Sie sich bitte auf andere Antworten in dieser Frage)


Beispiele:

Folgende Beispiele wurden zitiert von verlinkte Seite, in der Hoffnung, Linkfäule zu verhindern.

Konfiguration

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

Was ist in der Tabelle?

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.

Holen Sie sich zuerst N Reihen

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

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Holen Sie sich zuerst N Zeilen, wenn Nth Reihe hat Verbindungen, erhalten alle gebundenen Reihen

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.

oben x% der Zeilen

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Verwendung eines Offsets, sehr nützlich für die Paginierung

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.

Sie können den Offset mit Prozentwerten kombinieren

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.

328
2017-09-26 04:01



Sie können dafür eine Unterabfrage verwenden

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

Schau dir auch das Thema an Auf ROWNUM und begrenzende Ergebnisse bei Oracle / AskTom für weitere Informationen.

Aktualisieren: Um das Ergebnis sowohl mit der unteren als auch der oberen Grenze zu begrenzen, werden die Dinge etwas aufgeblähter

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;

(Aus dem angegebenen AskTom-Artikel kopiert)

Update 2: Ab Oracle 12c (12.1) steht eine Syntax zur Verfügung, um Zeilen zu begrenzen oder bei Offsets zu beginnen.

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

Sehen diese Antwort für mehr Beispiele. Danke an Krumia für den Hinweis.


713
2018-01-22 19:55



Ich habe einige Leistungstests für die folgenden Ansätze durchgeführt:

Asktom

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

Kurze Alternative

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Ergebnisse

Tabelle hatte 10 Millionen Datensätze, Sortierung war in einer nichtindexierten Datetime-Zeile:

  • Explain-Plan zeigte für alle drei Selects denselben Wert (323168)
  • Aber der Gewinner ist AskTom (mit analytischen Folgen dicht dahinter)

Die Auswahl der ersten 10 Zeilen dauerte:

  • AskTom: 28-30 Sekunden
  • Analytisch: 33-37 Sekunden
  • Kurze Alternative: 110-140 Sekunden

Zeilen zwischen 100.000 und 100.010 auswählen:

  • AskTom: 60 Sekunden
  • Analytisch: 100 Sekunden

Auswählen von Zeilen zwischen 9.000.000 und 9.000.010:

  • AskTom: 130 Sekunden
  • Analytisch: 150 Sekunden

166
2018-06-30 14:20



Eine analytische Lösung mit nur einer verschachtelten Abfrage:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() könnte ersetzt werden Row_Number() Sie können jedoch mehr Datensätze zurückgeben als erwartet, wenn doppelte Werte für name vorhanden sind.


52
2018-01-23 14:28



Auf Oracle 12c (siehe Zeilenbegrenzungsklausel in SQL-Referenz):

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

27
2017-09-24 03:09



Paginierungsabfragen mit Bestellungen sind in Oracle wirklich knifflig.

Oracle stellt eine ROWNUM-Pseudospalte zur Verfügung, die eine Zahl zurückgibt, die die Reihenfolge angibt, in der die Datenbank die Zeile aus einer Tabelle oder Gruppe von verbundenen Ansichten auswählt.

ROWNUM ist eine Pseudospalte, die viele Menschen in Schwierigkeiten bringt. Ein ROWNUM-Wert ist keiner Zeile dauerhaft zugewiesen (dies ist ein häufiges Missverständnis). Es kann verwirrend sein, wenn ein ROWNUM-Wert tatsächlich zugewiesen wird. Ein ROWNUM-Wert wird einer Zeile zugewiesen nachdem es Filterprädikate passiert hat der Abfrage aber vor Abfrage Aggregation oder Sortierung.

Darüber hinaus wird ein ROWNUM-Wert erst erhöht, nachdem er zugewiesen wurde.

Aus diesem Grund gibt die folgende Abfrage keine Zeilen zurück:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

Die erste Zeile des Abfrageergebnisses übergibt das Prädikat ROWNUM> 1 nicht, sodass ROWNUM nicht auf 2 erhöht wird. Aus diesem Grund wird kein ROWNUM-Wert größer als 1, daher gibt die Abfrage keine Zeilen zurück.

Die korrekt definierte Abfrage sollte folgendermaßen aussehen:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

Weitere Informationen zu Paginierungsabfragen finden Sie in meinen Artikeln auf Vertabelo Blog:


10
2018-04-12 17:32



Weniger SELECT-Anweisungen Außerdem weniger Leistung. Dank an: anibal@upf.br

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

7
2018-03-02 14:32