Как ограничить количество строк, возвращаемых запросом Oracle после заказа?

есть ли способ сделать Oracle запрос ведет себя так, как будто он содержит MySQL limit предложения?

на MySQL, Я могу сделать это:

select * 
from sometable
order by name
limit 20,10

чтобы получить 21-й до 30-го строк (пропустить первые 20, дать следующие 10). Строки выбираются после order by, так что это действительно начинается с 20-го имени в алфавитном порядке.

на Oracle, единственное, что люди упоминают это rownum псевдо-столбец, но он оценивается доorder by, что означает это:

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

вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не то, что я хочу. Он также не позволяет указать смещение.

4 828
sql system.data.oracleclient limit pagination

4 ответа:

начиная с Oracle 12c R1 (12.1), там и a предложение ограничения строки. Он не использует знакомые LIMIT синтаксис, но он может сделать работу лучше, с большим количеством опций. Вы можете найти полный синтаксис здесь.

чтобы ответить на исходный вопрос, вот запрос:

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

(для более ранних версий Oracle, пожалуйста, обратитесь к другим ответам в этом вопрос)

примеры:

следующие примеры были приведены из ссылке, в надежде предотвратить гниль связи.

Setup

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

COMMIT;

что в таблице?

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.

первый N строки

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

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

5 rows selected.

первый N строк если Nе строка имеет связи, получить все связанные строки

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% от строки

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

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

4 rows selected.

использование смещения, очень полезно для разбиения на страницы

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.

вы можете комбинировать смещение с процентами

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

вы можете использовать подзапрос для этого, как

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

есть также посмотреть на тему на ROWNUM и ограничивающие результаты в Oracle/AskTom для получения дополнительной информации.

обновление: Чтобы ограничить результат как нижними, так и верхними границами, вещи становятся немного более раздутыми с

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;

(скопировано с указанного AskTom-article)

обновление 2: Начиная с Oracle 12c (12.1) существует синтаксис, доступный для ограничьте строки или начните со смещений.

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

посмотреть ответ для получения дополнительных примеров. Спасибо Крумии за подсказку.

723

Я провел тестирование производительности для следующих подходов:

Asktom

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

аналитическая

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

Короткое Альтернатива

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

результаты

таблица имела 10 миллионов записей, сортировка была на неиндексированной строке datetime:

  • объяснить план показал то же значение для всех трех выбирает (323168)
  • но победитель AskTom (с аналитически следующим рядом сзади)

Выбор первых 10 строк взял:

  • AskTom: 28-30 секунд
  • аналитическая: 33-37 секунд
  • короткая альтернатива: 110-140 секунд

выбор строк между 100,000 и 100,010:

  • AskTom: 60 секунд
  • аналитический: 100 секунд

выбор строк между 9,000,000 и 9,000,010:

  • AskTom: 130 секунды
  • аналитический: 150 секунд
169

аналитическое решение с одним вложенным запросом:

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

Rank() можно заменить на Row_Number() но может вернуть больше записей, чем вы ожидаете, если есть повторяющиеся значения для имени.

53