Czy istnieje sposób, aby Oraclezapytanie zachowywało się tak, jakby zawierało MySQL limitklauzulę?

W MySQLmogę to zrobić:

select * 
from sometable
order by name
limit 20,10

aby dostać 21 do 30 rzędów (pomiń pierwsze 20, podaj kolejne 10). Wiersze są wybierane po order by, więc tak naprawdę zaczyna się od 20. nazwy alfabetycznie.

W Oracle, jedyną rzeczą, ludzie wspomnieć, jest rownumpseudo-kolumny, ale to jest oceniane przed order by , która oznacza:

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

zwróci losowy zestaw dziesięciu wierszy uporządkowanych według nazwy, co zwykle nie jest tym, czego chcę. Nie pozwala również na określenie przesunięcia.

Począwszy od Oracle 12c R1 (12,1), nie jest rząd ograniczenie zapis . Nie korzysta ze znanej LIMITskładni, ale może wykonać to zadanie lepiej dzięki większej liczbie opcji. Możesz znaleźć pełną składnię tutaj . (Przeczytaj także więcej o tym, jak to działa wewnętrznie w Oracle w tej odpowiedzi ).

Aby odpowiedzieć na oryginalne pytanie, oto zapytanie:

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

(W przypadku wcześniejszych wersji Oracle zapoznaj się z innymi odpowiedziami na to pytanie)

Przykłady:

Poniższe przykłady zostały przytoczone z połączonej strony , w nadziei, że zapobiegną gniciu linków.

Ustawiać

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

COMMIT;

Co jest w tabeli

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.

Zdobądź pierwsze Nrzędy

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

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

5 rows selected.

Uzyskaj pierwsze Nwiersze, jeśli N^th wiersz ma więzi, uzyskać wszystkie związane wiersze

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.

Górny x% wierszy

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

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

4 rows selected.

Używanie przesunięcia, bardzo przydatne do stronicowania

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.

Możesz połączyć przesunięcie z wartościami procentowymi

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.

W tym celu można użyć podzapytania

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

Więcej informacji można znaleźć w temacie O ROWNUM i ograniczaniu wyników w Oracle / AskTom.

Aktualizacja : Aby ograniczyć wynik zarówno z dolną, jak i górną granicą, rzeczy stają się bardziej rozdęte

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;

(Skopiowano z określonego artykułu AskTom)

Aktualizacja 2 : Począwszy od Oracle 12c (12.1) dostępna jest składnia ograniczająca wiersze lub rozpoczynająca się od przesunięć.

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

Zobacz tę odpowiedź, aby uzyskać więcej przykładów. Dzięki Krumii za podpowiedź.

Przeprowadziłem testy wydajności dla następujących podejść:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Analityczny

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

Krótka alternatywa

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

Wyniki

Tabela miała 10 milionów rekordów, sortowanie było w nieindeksowanym wierszu daty i godziny:

• Wyjaśnij plan pokazał tę samą wartość dla wszystkich trzech wybranych (323168)
• Ale zwycięzcą jest AskTom (z analizą podążającą tuż za)

Wybór pierwszych 10 wierszy zajął:

• AskTom: 28-30 sekund
• Analityczne: 33-37 sekund
• Krótka alternatywa: 110-140 sekund

Wybieranie wierszy od 100 000 do 100 010:

• AskTom: 60 sekund
• Analityczny: 100 sekund

Wybieranie wierszy od 9 000 000 do 9 000 010:

• AskTom: 130 sekund
• Analityczny: 150 sekund

Rozwiązanie analityczne z tylko jednym zagnieżdżonym zapytaniem:

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

Rank()może zostać zastąpione, Row_Number()ale może zwrócić więcej rekordów niż się spodziewasz, jeśli dla nazwy istnieją zduplikowane wartości.

W Oracle 12c (patrz klauzula ograniczająca wiersze w dokumentacji SQL ):

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

Zapytania dotyczące paginacji z kolejnością są w Oracle bardzo trudne.

Oracle udostępnia pseudokolumnę ROWNUM, która zwraca liczbę wskazującą kolejność, w jakiej baza danych wybiera wiersz z tabeli lub zestawu połączonych widoków.

ROWNUM to pseudokolumna, która wpędza wielu ludzi w kłopoty. Wartość ROWNUM nie jest na stałe przypisywana do wiersza (jest to powszechne nieporozumienie). Może być mylące, gdy faktycznie przypisano wartość ROWNUM. Wartość ROWNUM jest przypisywana do wiersza po przejściu predykatów filtru zapytania, ale przed agregacją lub sortowaniem zapytania .

Co więcej, wartość ROWNUM jest zwiększana dopiero po przypisaniu.

Dlatego zapytanie następujące nie zwraca żadnych wierszy:

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

Pierwszy wiersz wyniku zapytania nie przechodzi predykatem ROWNUM> 1, więc ROWNUM nie zwiększa się do 2. Z tego powodu żadna wartość ROWNUM nie jest większa niż 1, w związku z czym zapytanie nie zwraca wierszy.

Prawidłowo zdefiniowane zapytanie powinno wyglądać następująco:

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

Dowiedz się więcej o zapytaniach dotyczących paginacji w moich artykułach na blogu Vertabelo :

• Wyjaśnienie Oracle ROWNUM
• Zapytania Top-N i paginacja

Standard SQL

Jak wyjaśniłem w tym artykule , SQL: 2008 Standard zapewnia następującą składnię w celu ograniczenia zestawu wyników SQL:

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

Oracle 11g i starsze wersje

Przed wersją 12c, aby pobrać rekordy Top-N, trzeba było użyć tabeli pochodnej i pseudokolumny ROWNUM:

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

Mniej instrukcji SELECT. Ponadto mniej energochłonne. Kredyty dla: anibal@upf.br

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

Jako rozszerzenie przyjętej odpowiedzi Oracle wewnętrznie korzysta z ROW_NUMBER/RANKfunkcji. OFFSET FETCHskładnia to cukier składniowy.

Można to zaobserwować, stosując DBMS_UTILITY.EXPAND_SQL_TEXTprocedurę:

Przygotowanie próbki:

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

Pytanie:

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

jest regularny:

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 <> demo skrzypiec

Pobieranie rozszerzonego tekstu SQL:

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 TIESjest rozwinięty jako 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

i przesunięcie:

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"

Jeśli nie korzystasz z Oracle 12C, możesz użyć zapytania TOP N, jak poniżej.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

Możesz nawet przenieść to z klauzuli z klauzulą ​​w następujący sposób

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

Tutaj tworzymy widok wbudowany i zmieniamy nazwę rownum na rnum. Możesz użyć rnum w głównym zapytaniu jako kryterium filtru.

Zacząłem przygotowywać się do egzaminu Oracle 1z0-047, sprawdzonego pod kątem 12c. Przygotowując się do niego, natknąłem się na ulepszenie 12c znane jako „FETCH FIRST”. Pozwala ono pobierać wiersze / ograniczać wiersze według własnego uznania. Dostępnych jest kilka opcji

- 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

Przykład:

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

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

większe niż wartości dowiedzieć się

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

dowiedz się mniej niż wartości

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

Dla każdego wiersza zwróconego przez zapytanie pseudokolumna ROWNUM zwraca liczbę wskazującą kolejność, w jakiej Oracle wybiera wiersz z tabeli lub zestawu połączonych wierszy. Pierwszy wybrany wiersz ma ROWNUM 1, drugi ma 2 i tak dalej.

  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 

Zaimplementowałem to na oracleserwerze11.2.0.1.0

W przypadku SQL-Developer automatycznie pobiera tylko pierwsze 50 wierszy. A jeśli przewiniemy w dół, pobierze kolejne 50 wierszy i tak dalej!

Dlatego nie musimy definiować, w przypadku narzędzia SQL Developer!

W wyroczni

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

VAL

    10
    10
     9
     9
     8

Wybrano 5 wierszy.

SQL>

(niesprawdzone) coś takiego może wykonać zadanie

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

Istnieje również ranga funkcji analitycznej, według której można sortować według.

To samo co powyżej z poprawkami. Działa, ale zdecydowanie nie jest ładna.

   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

Szczerze mówiąc, lepiej skorzystać z powyższych odpowiedzi.