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

Paginierungsabfragen mit Bestellung sind in Oracle wirklich schwierig.

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 einem Satz von verbundenen Ansichten auswählt.

ROWNUM ist eine Pseudo-Kolumne, die viele Leute in Schwierigkeiten bringt. Ein ROWNUM-Wert wird einer Zeile nicht 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 bestanden hat der Abfrage, sondern vor der Aggregation oder Sortierung von Abfragen .

Außerdem wird ein ROWNUM-Wert erst dann erhöht, wenn 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 erfüllt das Prädikat ROWNUM > 1 nicht, so dass ROWNUM nicht auf 2 erhöht wird. Aus diesem Grund wird kein ROWNUM-Wert größer als 1, und die Abfrage liefert folglich keine Zeilen.

Eine korrekt definierte Abfrage sollte wie folgt aussehen:

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

Weitere Informationen über Paginierungsabfragen finden Sie in meinen Artikeln über Vertabelo Blog:

• Oracle ROWNUM Erklärt
• Top-N- und Paginierungsabfragen

Answer 2

Als eine Erweiterung von akzeptierte Antwort Oracle verwendet intern ROW_NUMBER/RANK Funktionen. OFFSET FETCH Syntax ist ein Syntaxzucker.

Dies kann beobachtet werden durch die Verwendung von DBMS_UTILITY.EXPAND_SQL_TEXT Verfahren:

Vorbereiten der Probe:

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

Abfrage:

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

ist regelmäßig:

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db<>fiddle demo

Abrufen von erweitertem SQL-Text:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

---

WITH TIES wird erweitert zu RANK :

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

und Versatz:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

Answer 3

In der Version 21c können Sie einfach einen Grenzwert wie folgt anwenden:

select * from course where ROWNUM <=10;

Answer 4

Weniger SELECT-Anweisungen. Außerdem wird weniger Leistung verbraucht. 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;

Answer 5

Ich habe mit der Vorbereitung auf die Oracle 1z0-047-Prüfung begonnen, validiert gegen 12c Während der Vorbereitung bin ich auf eine 12c-Erweiterung gestoßen, die als 'FETCH FIRST' bekannt ist. Sie ermöglicht es Ihnen, Zeilen abzurufen / zu begrenzen, wie es Ihnen beliebt. Mehrere Optionen sind damit verfügbar

- 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

Beispiel:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY