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.
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 N
TH 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.
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ź.
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
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.
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