Как ограничить количество строк, возвращаемых запросом 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

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

22.01.2009 19:48:43
Стандартизировано в SQL: 2008.
dalle 26.01.2009 14:18:21
Том Кайт объявил о пределе для Oracle 12c ...
wolφi 3.12.2012 11:44:54
Получение следующей страницы в наборе результатов?
Mathieu Longtin 16.12.2013 16:33:29
@YaroslavShabalin В частности, поисковый запрос использует этот шаблон все время. Практически любое приложение с любой функцией поиска будет использовать его. Другим вариантом использования будет загрузка только части длинного списка или клиентской части таблицы и предоставление пользователю возможности расширения.
jpmc26 14.08.2014 19:19:10
@YaroslavShabalin Вы не можете получить другой набор результатов, если базовые данные не изменятся из-за ORDER BY. Вот и весь смысл заказа в первую очередь. Если базовые данные изменяются, и ваш набор результатов изменяется из-за этого, то почему бы не показать пользователю обновленные результаты вместо устаревшей информации? Кроме того, государственное управление - это чума, которую следует избегать, насколько это возможно. Это постоянный источник осложнений и ошибок; Вот почему функционал становится таким популярным. И когда бы вы знали, чтобы истечь весь набор результатов в памяти? В Интернете у вас нет возможности узнать, когда пользователь уходит.
jpmc26 15.08.2014 15:03:54
16 ОТВЕТОВ
РЕШЕНИЕ

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

Чтобы ответить на оригинальный вопрос, вот запрос:

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

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


Примеры:

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

Настроить

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.

Верх 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.
601
3.02.2020 09:18:51
Просто для расширения: OFFSET FETCHсинтаксис является синтаксическим сахаром. Подробности
Lukasz Szozda 18.08.2019 19:03:56

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

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-статьи)

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

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

Посмотрите этот ответ для большего количества примеров. Спасибо Крумии за подсказку.

789
25.10.2014 19:18:07
Это, безусловно, способ сделать это, но имейте в виду (как говорится в статье о спросе), производительность запросов снижается с увеличением вашего максимального значения. Это хорошее решение для результатов запросов, когда вы хотите видеть только первые несколько страниц, но если вы используете это в качестве механизма для кодирования страниц по всей таблице, вам лучше будет рефакторинг вашего кода
Chris Gill 27.08.2009 12:30:53
+1 Ваша нижняя / верхняя версия фактически помогла мне обойти проблему, когда простое ограниченное сверху предложение rownum резко замедлило мой запрос.
Kelvin 9.08.2011 22:21:18
Ли Рифель "аналитическое решение только с одним вложенным запросом" является тем.
Darren Hicks 27.03.2012 23:22:07
В статье AskTom также есть подсказка оптимизатора, в которой используется SELECT / * + FIRST_ROWS (n) / a. , rownum rnum Перед косой чертой должна стоять звездочка. ТАК чистит это.
David Mann 5.03.2013 15:34:10
Обратите внимание, что для Oracle 11 внешний SELECT с ROWNUM не позволит вам вызвать deleteRow для UpdatableResultSet (с ORA-01446) - ожидая этого изменения 12c R1!
nsandersen 11.05.2015 08:34:27

(не проверено) что-то подобное может сделать работу

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

Существует также ранг аналитической функции, который вы можете использовать для упорядочения.

-4
22.01.2009 20:21:56
Это не вернет ни одной строки, поскольку ROWNUM является столбцом в наборе результатов, поэтому последнее условие WHERE всегда будет ложным. Кроме того, вы не можете использовать ROWNUM и ЗАКАЗАТЬ ПО ЗАКАЗУ.
Ben 8.09.2013 12:20:36
Отлично. Давайте оставим это здесь как предупреждение для других.
EvilTeach 21.01.2014 15:33:25

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

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

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

55
14.01.2016 10:52:18
Я люблю аналитику. Возможно, вы захотите уточнить, в чем разница в поведении между Rank () и Row_Number ().
Dave Costa 23.01.2009 16:53:57
Действительно, не уверен, почему я не думал о дубликатах. Таким образом, в этом случае, если для имени есть повторяющиеся значения, RANK может дать больше записей, чем вы ожидаете, поэтому вам следует использовать Row_Number.
Leigh Riffel 26.01.2009 14:11:03
При упоминании rank()этого также стоит отметить, dense_rank()что может быть более полезным для управления выводом, так как последний не «пропускает» числа, тогда как rank()может. В любом случае для этого вопроса row_number()лучше всего подходит. Еще одно - этот метод применим к любым БД, поддерживающим упомянутые функции.
Used_By_Already 29.10.2017 00:34:39

То же, что и выше с исправлениями. Работает, но определенно не красиво.

   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

Честно говоря, лучше использовать приведенные выше ответы.

-5
18.11.2010 17:17:03
Это неверно, поскольку предложение WHERE оценивается перед ORDER BY.
Ben 8.09.2013 12:21:30
Интересно украден у меня плохой ответ ниже.
EvilTeach 15.09.2015 12:30:32

Менее SELECT заявления. Кроме того, меньше потребляет производительность. Кредиты для: anibal@upf.br

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;
5
2.03.2011 14:40:32
Кроме того, это совершенно неверный ответ. Вопрос был об ограничении ПОСЛЕ сортировки. Так что rownum должен быть вне подзапроса.
BitLord 18.09.2017 07:20:24

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

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 миллионов записей, сортировка осуществлялась по неиндексированной строке даты и времени:

  • План объяснения показал одинаковое значение для всех трех вариантов (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 секунд
185
29.06.2018 09:26:59
Хорошо сделано. Вы пробовали короткую альтернативу с промежуточным вместо> = и <=?
Mathieu Longtin 5.07.2011 14:55:44
@MathieuLongtin BETWEEN- это просто сокращение для >= AND <=( stackoverflow.com/questions/4809083/between-clause-versus-and )
wweicker 20.10.2011 15:27:25
zeldi - На какой версии это было? Oracle внесла аналитические улучшения производительности в 11.1. и 11.2.
Leigh Riffel 26.09.2012 12:03:28
@Leigh Riffel Это было 10.2.0.5; Однажды я мог бы потратить время, а также проверить версию 11i.
zeldi 11.03.2013 09:17:05
Я провел несколько быстрых тестов и получил аналогичные результаты для 12c. Новый offsetсинтаксис имеет тот же план и производительность, что и аналитический подход.
Jon Heller 18.01.2014 04:17:22

В Oracle 12c (см. Предложение по ограничению строк в справочнике по SQL ):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
29
27.11.2014 07:16:57
И, конечно же, им пришлось использовать совершенно другой синтаксис, чем все остальные
Mathieu Longtin 25.09.2013 01:12:42
Очевидно, после того, как LIMITони сошлись со всеми другими поставщиками, чтобы договориться в SQL: 2008, им пришлось взять листок из книги Microsoft и нарушить стандарт.
beldaz 25.09.2013 01:39:16
Интересно, что недавно я слышал, что самый последний стандарт включает этот синтаксис, так что, возможно, Oracle перед этим внедрил его. Возможно, это более гибкий, чемLIMIT ... OFFSET
beldaz 31.12.2013 23:08:04
@Derek: Да, несоблюдение стандарта вызывает сожаление. Но недавно представленная функциональность в 12cR1 более мощная, чем просто LIMIT n, m(см. Мой ответ). Опять же, Oracle должен был быть реализован LIMIT n, mкак синтаксический сахар, как это эквивалентно OFFSET n ROWS FETCH NEXT m ROWS ONLY.
sampathsris 26.09.2014 13:54:21
@Derek: На самом деле, я только что заметил это замечание в руководстве PostgreSQL postgresql.org/docs/9.0/static/sql-select.html#AEN69535 "Предложения LIMIT и OFFSET - это синтаксис, специфичный для PostgreSQL, также используемый MySQL. SQL Стандарт 2008 ввел пункты OFFSET ... FETCH {FIRST | NEXT} ... для той же функциональности ". Таким образом, LIMIT никогда не был частью стандарта.
beldaz 21.05.2015 00:58:42

Запросы на нумерацию страниц с упорядочением действительно сложны в Oracle.

Oracle предоставляет псевдостолбец ROWNUM, который возвращает число, указывающее порядок, в котором база данных выбирает строку из таблицы или набора объединенных представлений.

ROWNUM - это псевдоколонка, которая доставляет многим людям неприятности. Значение ROWNUM не всегда назначается строке (это распространенное недоразумение). Это может сбивать с толку, когда значение ROWNUM фактически назначается. Значение ROWNUM присваивается строке после прохождения предикатов фильтра запроса, но до агрегирования или сортировки запроса .

Более того, значение ROWNUM увеличивается только после его назначения.

Вот почему следующий запрос не возвращает строк:

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

Первая строка результата запроса не передает предикат ROWNUM> 1, поэтому ROWNUM не увеличивается до 2. По этой причине никакое значение ROWNUM не будет больше 1, следовательно, запрос не возвращает строк.

Правильно определенный запрос должен выглядеть так:

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

Узнайте больше о запросах на нумерацию страниц в моих статьях в блоге Vertabelo :

14
12.04.2014 17:32:50
Первая строка результата запроса не проходит предикат ROWNUM> 1 (…) - повышенное значение для объяснения этого.
Piotr Dobrogost 13.03.2019 09:11:12

В оракуле

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

VAL

    10
    10
     9
     9
     8

5 строк выбрано.

SQL>

-3
20.08.2014 18:33:14
Вы должны указать, что это применимо, начиная с Oracle 12c, и что вы копируете / вставляете это откуда-то - пожалуйста, всегда указывайте свои источники.
Mat 21.08.2014 09:55:49
Источник это @Mat. И Ракеш, пожалуйста, постарайтесь хотя бы адаптировать ответ к исходному вопросу. Я также дал ответ со ссылкой на тот же источник, но я постарался быть исчерпывающим и привел исходный источник.
sampathsris 26.09.2014 14:03:34

Я начал готовиться к экзамену Oracle 1z0-047, проверенному на соответствие 12c. При подготовке к нему я столкнулся с расширением 12c, известным как «FETCH FIRST». Оно позволяет вам выбирать строки / ограничивать строки по вашему усмотрению. Несколько вариантов доступны с ним

- 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

Пример:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY
2
1.06.2016 10:31:02
stackoverflow.com/a/26051830/635608 - это уже было предоставлено в других ответах. Пожалуйста, воздержитесь от публикации материалов, которые уже были опубликованы несколько месяцев назад.
Mat 1.06.2016 11:43:41
о, конечно, я не нашел ответы на все вопросы, я встречался с подзапросами на ранней стадии, буду помнить об этом.
arjun gaur 1.06.2016 12:48:47

Если вы не используете Oracle 12C, вы можете использовать запрос TOP N, как показано ниже.

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

Вы даже можете переместить это из предложения в предложение следующим образом

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

Здесь на самом деле мы создаем встроенное представление и переименовываем rownum в rnum. Вы можете использовать rnum в основном запросе в качестве критерия фильтрации.

3
8.12.2016 10:11:23
В моем случае это не вернуло правильные строки. То, что я сделал, чтобы исправить это, чтобы сделать ORDER BYи rownumотдельно. По сути, я создал подзапрос с ORDER BYпредложением .
Patrick Gregorio 10.05.2018 20:28:56
Даунвот как неверный ответ. Вопрос был об ограничении после сортировки, поэтому rownumдолжен быть за пределами подзапроса.
Piotr Dobrogost 13.03.2019 09:06:52
@PiotrDobrogost rownum только снаружи.
sandi 30.09.2019 08:49:50
select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

больше, чем значения узнают

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

меньше, чем значения узнают

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5
1
12.04.2017 11:01:46
Downvote как ROW_NUMBER()основанное решение уже было опубликовано Ли Риффелем. В зависимости есть синтаксические ошибки в показанном коде.
Piotr Dobrogost 13.03.2019 08:33:12

Для каждой строки, возвращаемой запросом, псевдостолбец ROWNUM возвращает число, указывающее порядок, в котором Oracle выбирает строку из таблицы или набора соединенных строк. Первый выбранный ряд имеет ROWNUM, равный 1, второй имеет 2 и т. Д.

  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 

Я реализовал это на oracleсервере11.2.0.1.0

1
13.03.2019 08:50:08
понижение, поскольку вопрос задает вопрос об ограничении упорядоченных рядов, а у вас даже нет порядка
Piotr Dobrogost 13.03.2019 08:23:20
@PiotrDobrogost Поймите, что это не огромная задача, упорядочивание ключевых слов является общим для всех rdbms, только ограничение имеет изменения.
Sumesh TG 13.03.2019 08:48:40

Стандарт SQL

Как я объяснил в этой статье , стандарт SQL: 2008 предоставляет следующий синтаксис для ограничения набора результатов SQL:

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

Oracle 11g и более ранние версии

До версии 12c для получения записей Top-N необходимо было использовать производную таблицу и псевдостолбец ROWNUM:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50
6
13.08.2019 19:48:03

В качестве расширения принятого ответа Oracle внутренне использует ROW_NUMBER/RANKфункции. OFFSET FETCHсинтаксис является синтаксисом сахара.

Это можно наблюдать с помощью DBMS_UTILITY.EXPAND_SQL_TEXTпроцедуры:

Подготовка образца:

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 DESC
FETCH FIRST 5 ROWS ONLY;

регулярно:

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

Извлечение расширенного текста 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 TIESрасширяется как 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

и смещение:

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"
5
18.08.2019 19:02:48