Wie kann ich die Anzahl der von einer Oracle-Abfrage zurückgegebenen Zeilen nach der Bestellung begrenzen?

Question

Gibt es eine Möglichkeit, eine Oracle Abfrage so verhalten, als enthalte sie eine MySQL limit Klausel?

In MySQL kann ich dies tun:

select * 
from sometable
order by name
limit 20,10

um die 21. bis 30. Reihe zu erhalten (die ersten 20 überspringen, die nächsten 10 geben). Die Zeilen werden nach der order by Die Liste beginnt also wirklich mit dem 20. Namen in alphabetischer Reihenfolge.

In Oracle ist das einzige, was die Leute erwähnen, die rownum Pseudo-Spalte, aber sie wird ausgewertet vor order by Dies bedeutet Folgendes:

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

gibt eine zufällige Menge von zehn nach Namen geordneten Zeilen zurück, was normalerweise nicht das ist, was ich will. Außerdem ist es nicht möglich, einen Offset festzulegen.

Answer 1

Sie können dafür eine Unterabfrage verwenden wie

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

Sehen Sie sich auch das Thema Über ROWNUM und begrenzte Ergebnisse bei Oracle/AskTom für weitere Informationen.

更新情報 : Um das Ergebnis sowohl nach unten als auch nach oben zu begrenzen, werden die Dinge etwas aufgebläht mit

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;

(Kopiert aus dem angegebenen AskTom-Artikel)

Aktualisierung 2 : Ab Oracle 12c (12.1) gibt es eine Syntax, um Zeilen zu begrenzen oder mit Offsets zu beginnen.

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

Siehe diese Antwort für weitere Beispiele. Danke an Krumia für den Hinweis.

Answer 2

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

Die Tabelle hatte 10 Millionen Datensätze, die Sortierung erfolgte nach einer nicht indizierten Datumszeile:

• Erklären Sie den Plan zeigte den gleichen Wert für alle drei Auswahlen (323168)
• Aber der Gewinner ist AskTom (dicht gefolgt von analytic)

Die Auswahl der ersten 10 Zeilen dauerte:

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

Auswahl von Zeilen zwischen 100.000 und 100.010:

• AskTom: 60 Sekunden
• Analytisch: 100 Sekunden

Auswahl von Zeilen zwischen 9.000.000 und 9.000.010:

• AskTom: 130 Sekunden
• Analytisch: 150 Sekunden

Answer 3

SQL-Standard

Seit Version 12c unterstützt Oracle den SQL:2008 Standard, der die folgende Syntax zur Begrenzung der SQL-Ergebnismenge bietet:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g und ältere Versionen

Vor Version 12c musste man zum Abrufen der Top-N Datensätze, mussten Sie eine abgeleitete Tabelle und die ROWNUM Pseudospalte:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

Answer 4

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 durch Row_Number() gibt aber möglicherweise mehr Datensätze zurück, als Sie erwarten, wenn es doppelte Werte für Name gibt.

Answer 5

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

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