Jak ograniczyć liczbę wierszy zwracanych przez zapytanie Oracle po złożeniu zamówienia?

Czy istnieje sposób, aby zapytanie Oracle zachowywało się tak, jakby zawierało klauzulę MySQL limit?

In MySQL, I can do this: 

select * 
from sometable
order by name
limit 20,10

Aby uzyskać od 21 do 30 wierszy (pomiń pierwsze 20, podaj następne 10). Wiersze są wybierane po order by, więc tak naprawdę zaczyna się od 20. nazwy Alfabetycznie.

W Oracle jedyną rzeczą, o której ludzie wspominają, jest pseudo-kolumna rownum, ale jest oceniana przed order by, co oznacza: 

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

Zwróci losowy zestaw dziesięć rzędów uporządkowanych według nazwy, co zwykle nie jest tym, czego chcę. Nie pozwala również na określenie offsetu.

Author: DineshDB, 0000-00-00
Source

4 answers

W Oracle 12c R1 (12.1) istnieje a klauzula ograniczająca wiersz. Nie używa znanej składni LIMIT, ale może wykonać zadanie lepiej z większą liczbą opcji. Pełną składnię znajdziesz tutaj .

Aby odpowiedzieć na oryginalne pytanie, oto zapytanie: 

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
[13]} (wcześniejsze wersje Oracle można znaleźć w innych odpowiedziach na to pytanie)

Przykłady:

Poniższe przykłady zostały przytoczone z linked strona , w nadziei na zapobieganie gniciu linków.

Setup 

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.

Get first N wiersze 

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

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

5 rows selected.

Get first N rows, if NTH wiersz ma krawaty, get all the tied rows 

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% wierszy 

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

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

4 rows selected.

Korzystanie z offsetu, bardzo przydatne do paginacji 

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żna połączyć offset z procentami 

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.
 351
Author: Krumia,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-03 05:03:14

Możesz użyć subquery dla tego typu 

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;
Po więcej informacji zajrzyj również do tematu na temat ROWNUM i ograniczanie wyników na stronie Oracle/AskTom.

Update : Aby ograniczyć wynik zarówno z dolną, jak i górną granicą, rzeczy stają się nieco bardziej nadęte za pomocą 

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;

(skopiowane z podanego artykułu AskTom)

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

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

Zobacz Ta ODPOWIEDŹ więcej przykładów. Dzięki Krumia za podpowiedź.

 723
Author: Kosi2801,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-25 19:18:07

Zrobiłem kilka testów 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

Analityczne 

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 niezindeksowanym wierszu datetime:

  • Explain plan pokazywał tę samą wartość dla wszystkich trzech selekcji (323168)
  • ale zwycięzcą jest AskTom (z analitykiem tuż za nim)

Wybór pierwszych 10 wierszy:

  • AskTom: 28-30 seconds
  • analityczne: 33-37 sekund
  • krótka alternatywa: 110-140 sekund

Wybór wierszy między 100 000 a 100 010:

  • AskTom: 60 sekund
  • analityczny: 100 sekund

Wybór wierszy między 9,000,000 a 9,000,010:

  • AskTom: 130 sekund
  • analityczny: 150 sekund
 169
Author: zeldi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-29 09:26:59

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 być podstawiony na Row_Number(), ale może zwrócić więcej rekordów, niż się spodziewasz, jeśli dla name są zduplikowane wartości.

 53
Author: Leigh Riffel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-14 10:52:18