Oracle ограничивает количество строк в выборке

Обновлено: 05.07.2024

До Oracle12c вам приходилось использовать специальные методы для отображения первых "n" строк в запросе.

Получение подмножеств данных в отсортированном порядке из базы данных всегда было проблематичным в SQL, и многие люди используют предложение "where rownum , n", случайным образом отображая строки по мере их нахождения в блоки данных.

У Oracle есть много способов отобразить первые n строк из таблицы, но ни один из них не имеет прямого синтаксиса, пока Oracle не представила функцию "ограничения строк" с синтаксисом "выборка:" и "смещение".

.Вот обзор методов выборки первых n SQL в Oracle:

  • План ограничения строк. Эта новая функция Oracle 12c «Смещение x выборка первых y строк» ​​позволяет легко отображать только первые n строк из таблицы. Именно этот метод мы обсудим ниже.

До Oracle 12c мы были ограничены следующими методами:

  • Top-n SQL с использованием подвыборки с ROWNUM. Вы можете использовать встроенное представление с ROWNUM, чтобы получить первые 10 строк для любого SQL-запроса, а rownum можно использовать для материализации встроенного представления. Остерегайтесь, это не всегда хорошо, как добавление предложения WHERE ROWNUM.
  • Top-n SQL с функциями плотности_rank и ранжирования SQL. Oracle SQL включает функции ранжирования, обеспечивающие поддержку стандартных ранжирований OLAP, таких как первые 10, последние 10, первые 10 процентов и последние 10 процентов.
  • Top-n SQL с использованием функции row_number. Вы можете запрашивать первые 100 строк, используя синтаксис Oracle row_number() и "over".

Давайте рассмотрим способ отображения первых n строк таблицы:

выбрать
*
из
(выбрать empno, sal row_number()
над
(упорядочить по
sal desc) rnk из emp) < br />где рнк ;

Это работает для отображения первых 10 строк из таблицы, но синтаксис загадочен, и в Oracle 12c мы получили расширение SQL, которое упрощает и упрощает отображение первых n строк из таблицы.

  • выбрать . . . . order by x fetch only first 10 rows: Это отобразит первые строки таблицы в порядке, указанном в предложении order by.

  • выбрать . . . порядок по смещению x 20 выборка только первых 10 строк: выполняется смещение в таблице (в отсортированном порядке), а затем выборка следующих 10 строк таблицы.

Рассмотрите следующие примеры:

-- ***************************
-- выборка первых 5 строк,
-- упорядочение по ename < br />-- ************************

выберите
имя
из
emp
упорядочить по имени
выбрать только первые 5 строк;

выбрать
ename
из
emp
упорядочить по ename
сместить 5 строк
выбрать только следующие 5 строк;

Здесь мы видим, что Oracle 12c снова расширил свой диалект SQL, чтобы упростить разбивку набора результатов SQL на страницы и уменьшить объем загадочного синтаксиса ANSI 99, который ранее требовался для отображения «следующие n» и «первые n». " строки из предварительно отсортированного набора результатов.

В PL/SQL программист может объявить курсор и получить страницу данных, используя синтаксис "выборки", и эта "выборка" SQL, по-видимому, имеет аналогичную функциональность. Хотя Oracle не публикует внутреннее устройство этих операндов смещения и выборки, они проявляются как «лимит строк» ​​в планах выполнения.

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

Получите полную
информацию о настройке Oracle SQL

Знаменитая книга "Расширенная настройка Oracle SQL. Полное руководство" содержит ценную информацию о настройке Oracle SQL. Эта книга включает сценарии и инструменты для повышения производительности Oracle 11g, и вы можете купить ее со скидкой 30 % непосредственно у издателя.


Бурлесон — американская команда


Примечание. Эта документация по Oracle была создана в качестве справочника по поддержке и обучению Oracle для использования нашими специалистами-консультантами по настройке производительности администраторов баз данных. Не стесняйтесь задавать вопросы на нашем форуме Oracle.

Проверьте опыт! Любой, кто рассматривает возможность использования услуг эксперта службы поддержки Oracle, должен самостоятельно проверить свои полномочия и опыт, а не полагаться на рекламу и самопровозглашенный опыт. Все законные эксперты Oracle публикуют свои квалификации Oracle.

Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:


и укажите URL-адрес страницы.


Burleson Consulting

Оракул поддержки баз данных

Возможно, вы используете функцию поиска в своем коде и хотите показать только часть всего набора результатов. Если ваш запрос возвращает сотни строк, но вы хотите отобразить только 10 за раз, вам понадобится такой запрос.

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

Метод MySQL — с использованием LIMIT

Если вы вообще использовали MySQL, возможно, вам знаком такой синтаксис:

Этот запрос получит строки с 51 по 60, упорядоченные по столбцу имени.

Это работает в MySQL, потому что ORDER BY выполняется перед LIMIT. Таким образом, вы можете получить строки с 51 по 60, используя это предложение LIMIT.

Однако в Oracle нет ключевого слова LIMIT, несмотря на то, что оно есть в стандарте SQL.

Если вы попробуете это сделать в Oracle, используя псевдостолбец ROWNUM, это не сработает. Это связано с тем, что ROWNUM оценивается перед ORDER BY.

Псевдостолбец ROWNUM не сохраняется в строке или таблице постоянно.

Допустим, у вас есть такой запрос:

У этого есть несколько проблем:

  • Он покажет случайный выбор из 10 записей, поскольку они не упорядочены во время применения предложения WHERE.
  • Также нельзя указать начальную точку или смещение (например, получить строки 51–60)

Итак, как нам добиться желаемых результатов?

Как выбрать первые N строк в Oracle SQL

Чтобы найти первые N строк в Oracle SQL, существует один рекомендуемый способ.

Этот запрос получит первые 10 записей. Важным моментом здесь является то, что он сначала использует подзапрос для упорядочивания, а затем внешний запрос выполняет ограничение по номеру строки.

Номер строки назначается после ORDER BY, поскольку он находится во внешнем запросе.

Вы можете изменить этот запрос в соответствии со своими потребностями.

Как написать запрос на разбиение на страницы Oracle

Еще одна распространенная причина использования этого типа запроса – разбиение на страницы.

Например, если вы хотите отобразить страницу 2 страницы результатов поиска, вы можете отобразить результаты с 11 по 20. Или с 26 по 50. Или что-то в этом роде.

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

Итак, как вы ограничиваете количество строк, возвращаемых запросом Oracle для разбиения на страницы?

Есть несколько способов сделать это.

Способ 1 – AskTom

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

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

  • FIRST_ROWS(n) называется подсказкой оптимизатора и сообщает Oracle, что вы хотите оптимизировать получение первых строк.
  • MAX_ROW_TO_FETCH — это последняя строка, которую вы хотите получить (например, если вы ищете строки с 51 по 60, установите для этого параметра значение 60).
  • MIN_ROW_TO_FETCH — это последняя строка, которую вы хотите получить (например, если вы ищете строки с 51 по 60, установите для этого параметра значение 51)

Метод 2 – Аналитический запрос

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

В этом запросе используется аналитическая функция ROW_NUMBER, которая возвращает номер строки для каждой строки, упорядоченной по указанному полю (в данном случае полю имени). Это также может работать в SQL Server.

Способ 3 — получение

В Oracle 12c появился новый метод ограничения строк или начала со смещения.

Этот запрос даст вам первые 10 строк, начиная с строки 51, поскольку к первым 50 строкам было применено «смещение».

Какой метод ограничения количества строк в Oracle является лучшим?

Что ж, лучший способ ограничения количества строк в Oracle будет учитывать производительность, гибкость и фактически используемую версию базы данных.

Если вы используете Oracle 12c, используйте синтаксис FETCH, так как он был создан специально для этой цели.

Если вы не используете Oracle 12c, я бы посоветовал использовать метод AskTom, поскольку он был рекомендован Томом Китом и использовался многими пользователями Oracle.

Итак, вот как вы можете написать запрос, чтобы ограничить количество строк, возвращаемых в Oracle. Если у вас есть какие-либо вопросы по этому поводу, дайте мне знать в разделе комментариев.

Наконец, если вам понравилась информация и советы по карьере, которые я предоставляю, подпишитесь на мою рассылку ниже, чтобы быть в курсе моих статей. Вы также получите фантастический бонус. Спасибо!

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

В MySQL я могу сделать следующее:

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

В Oracle упоминается только псевдостолбец rownum, но он оценивается перед порядком с помощью , что означает следующее:

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

@YaroslavShabalin В частности, постраничный поиск использует этот шаблон всегда. Почти любое приложение с любой функцией поиска будет использовать его. Другим вариантом использования может быть загрузка только части длинного списка или таблицы на стороне клиента и предоставление пользователю возможности расширения.

@YaroslavShabalin Вы не можете получить другой набор результатов, если базовые данные не изменились из-за ORDER BY . Вот и весь смысл сначала заказывать. Если базовые данные изменяются, и из-за этого меняется ваш набор результатов, то почему бы не показывать пользователю обновленные результаты вместо устаревшей информации? Кроме того, государственное управление — это чума, которой следует избегать как можно больше. Это постоянный источник сложностей и ошибок; Вот почему функционал становится таким популярным. И когда вы узнаете об истечении срока действия всего набора результатов в памяти? В Интернете у вас нет возможности узнать, когда пользователь уходит.

14 ответов 14

Для этого можно использовать подзапрос

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

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

(Скопировано из указанной статьи AskTom)

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

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

Это определенно способ сделать это, но имейте в виду (как говорится в статье ask tom), что производительность запроса ухудшается по мере увеличения вашего max rownum. Это хорошее решение для результатов запроса, когда вы хотите видеть только первые несколько страниц, но если вы используете это как механизм для кода, чтобы просмотреть всю таблицу, вам лучше реорганизовать свой код

+1 ваша нижняя/верхняя версия на самом деле помогла мне решить проблему, из-за которой простое предложение rownum с верхней границей резко замедляло мой запрос.

В статье AskTom также есть подсказка для оптимизатора, в которой используется SELECT /*+ FIRST_ROWS(n) / a., rownum rnum Перед закрывающей косой чертой должна стоять звездочка. SO вычищает его.

Обратите внимание, что для Oracle 11 внешний SELECT с ROWNUM не позволит вам вызвать deleteRow для UpdatableResultSet (с ORA-01446) — с нетерпением жду этого изменения 12c R1!

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

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

(Более ранние версии Oracle см. в других ответах на этот вопрос)

Примеры:

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

Настройка

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

Получить первые N строк

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

Вверху x % строк

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

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


@Pra_A В 11G нет встроенной поддержки LIMIT / OFFSET . Если вы проверите другие ответы, все они так или иначе фактически реализовали ограничение и смещение.

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

Асктом

Аналитический

Краткий вариант

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

  • План объяснения показал одинаковое значение для всех трех вариантов выбора (323 168)
  • Но победителем стал AskTom (с небольшим отрывом от аналитики)

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

Выбор строк от 100 000 до 100 010:

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

Выбор строк от 9 000 000 до 9 000 010:

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

zeldi — В какой версии это было? Oracle улучшила аналитическую производительность в версии 11.1. и 11.2.

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

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

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


Я люблю аналитику.Возможно, вы захотите уточнить, какая разница в поведении между Rank() и Row_Number().

Действительно, не знаю, почему я не подумал о дубликатах. Итак, в этом случае, если для имени есть повторяющиеся значения, RANK может дать больше записей, чем вы ожидаете, поэтому вам следует использовать Row_Number.

Если говорить о rank(), то стоит также упомянуть о плотности_rank(), которая может быть более полезной для управления выводом, поскольку последняя не "пропускает" числа, в то время как rank() может. В любом случае для этого вопроса лучше всего подходит row_number(). Еще одно не то, что этот метод применим к любой базе данных, которая поддерживает упомянутые функции.

В Oracle 12c (см. пункт об ограничении строк в справочнике по SQL):


Очевидно, что после того, как все другие производители договорились о LIMIT в SQL:2008, им пришлось взять листок из книги Microsoft и нарушить стандарт.

Интересно, что я недавно слышал, что самый последний стандарт включает этот синтаксис, так что, возможно, Oracle сначала ввела его, прежде чем внедрять. Возможно, он более гибкий, чем LIMIT . СМЕЩЕНИЕ

@Derek: Да, несоблюдение стандарта вызывает сожаление. Но недавно введенная функциональность в 12cR1 более мощная, чем просто LIMIT n, m (см. мой ответ). Опять же, Oracle должен был реализовать LIMIT n, m как синтаксический сахар, поскольку он эквивалентен OFFSET n ROWS FETCH NEXT m ROWS ONLY .

Стандарт SQL

Начиная с версии 12c, Oracle поддерживает стандарт SQL:2008, который обеспечивает следующий синтаксис для ограничения набора результатов SQL:

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

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


Мне любопытно, в Oracle никогда не было синтаксиса, поддерживающего использование "выбрать TOP N * из " или что-то в этом роде?

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

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

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

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

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

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

Существует два подхода к ограничению количества строк, возвращаемых запросом Oracle. Использование значения rownum для ограничения количества строк, а также ключевых слов OFFSET и FETCH для ограничения количества строк. Предложение ограничения строк поддерживается в запросах Oracle, начиная с Oracle 12c R1 (12.1). Oracle не использует ключевое слово LIMIT.

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

В Oracle вы можете использовать псевдостолбец ROWNUM, чтобы ограничить количество строк, возвращаемых запросом. Псевдостолбец ROWNUM в Oracle возвращает число, указывающее порядок, в котором база данных выбирает строку из таблицы. Значение ROWNUM присваивается строке после того, как она прошла предикаты фильтра запроса, но до объединения или сортировки запроса.

Проблема в ограничении количества строк

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

Решение 1

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

Если вам нужно получить строки для разбиения на страницы, укажите количество строк между результатами. Необходимо указать первую строку и количество строк.

Решение 2

Предложение ограничения строк поддерживается в запросах Oracle, начиная с Oracle 12c R1 (12.1).Oracle не использует ключевое слово LIMIT; вместо этого используются ключевые слова OFFSET и FETCH. В приведенном ниже запросе будет использоваться смещение и выборка, чтобы ограничить количество возвращаемых строк.

Приведенный выше запрос ограничит количество строк до первых 10 строк. Приведенный ниже запрос вернет ограничение на количество строк разбиения на страницы между строками.

Решение 3

В решении 2 запрос вернет ровно столько строк, сколько указано в выборке. При наличии повторяющихся значений запрос не вернет все строки.

Например, если 15 сотрудников имеют одинаковые имена в таблице сотрудников, запрос выберет первых 10 сотрудников. Если вам нужен полный список всех 15 сотрудников, то в запросе следует использовать опцию with ties.

Если в имени сотрудника нет дубликатов, возвращается только 10 строк.

Решение 4

Oracle поддерживает ограничение количества строк в процентах. Приведенный ниже запрос вернет верхние 10 процентов строк в качестве выходных данных. Это не будет ограничивать количество строк. Запрос извлекает все строки, которые входят в первые 10 процентов.

Решение 5

В результатах n процентов можно использовать смещение. Запрос вернет n% строк. Смещение будет включено в результат запроса. Используя этот запрос, вы можете получить нижнюю границу строк внутри процентного результата.

Решение 6

Функция row number() используется для упорядочения строк. Порядок строк будет использоваться для извлечения и ограничения количества строк, возвращаемых Oracle. В приведенном ниже запросе показано, как сортировать и ограничивать строки. Таким образом, запрос будет жестким.

Читайте также: