Oracle ограничить количество строк в выборке
В этом учебном материале вы узнаете, как использовать SQL функцию COUNT с синтаксисом и примерами.
Описание
SQL функция COUNT используется для подсчета количества строк, возвращаемых в операторе SELECT.
Использование смещения, очень полезно для нумерации страниц
Получить первые N строки, если N й строки имеет связи, получить все связанные строки
setFirstResult И setMaxResults Query методы
Для JPA и Hibernate Query , то setFirstResult метод является эквивалентом OFFSET , а setMaxResults метод является эквивалентом LIMIT:
Asktom
Примеры:
Следующие примеры были процитированы со ссылочной страницы в надежде предотвратить гниение ссылок.
PL/SQL
Я хочу использовать синтаксис Oracle, чтобы выбрать только 1 строку из таблицы DUAL . Например, я хочу выполнить такой запрос:
. и было бы около 40 записей. Но мне нужна только одна запись. . И я хочу, чтобы это произошло без WHERE оговорок.
Мне нужно что-то в поле table_name, например:
Какая версия Oracle? Использование ROWNUM или ROW_NUMBER (9i +) означало бы необходимость предложения WHERE
Вы действительно пробовали бежать select user from dual ? Если нет, попробуйте это и посмотрите, что у вас получится. В стандартной системе Oracle вы вернетесь пользователя, с которым выполняете команду.
Вы используете ROWNUM.
@ypercube, насколько я могу судить, это так. (По крайней мере, это работает для моей установки oracle10g.)
Да. ROWNUM - это специальный столбец, который добавляется к набору результатов и перечисляет результаты. Вы также можете использовать его, чтобы выбрать несколько, например, если вы хотите найти 10 самых высокооплачиваемых сотрудников, вы можете сказать: «ВЫБРАТЬ пользователя ИЗ СОТРУДНИКОВ, ГДЕ ROWNUM
Я нашел это "решение" спрятанным в одном из комментариев. Поскольку я некоторое время искал это, я хотел бы немного выделить его (пока не могу комментировать или делать такие вещи . ), поэтому я использовал следующее:
Это напечатает мне желаемую запись [Столбец] из самой новой записи в таблице, предполагая, что [Дата] всегда вставляется через SYSDATE.
Я обнаружил, что это также будет работать, если вы сделаете заказ ROWID , если вы никогда не удаляете какие-либо записи и всегда заботитесь о последней вставленной / измененной.
@vapcguy: Не ждите, что ROWID будет заказан, даже если вы никогда не удаляете строку из таблицы! Даже если это сработает для вас сейчас, никогда не будет гарантировано работать в будущих версиях.
@ D.Mika На самом деле, если он работает сейчас, и вы никогда не добавляете / не удаляете / не обновляете / не удаляете записи, проблем быть не должно. Записи могут быть изменены только в том случае, если вы действительно их измените. Существует это заблуждение, которое каким-то образом ROWID случайно модифицируется Oracle. Это не так. Он основан на фактическом изменении строк, то есть вы удаляете одну, а затем вставляете ее. Вставленный получит старый ROWID . Есть такие вещи, как статические таблицы, которые никогда не обновляются, как в США, что является хорошим примером. Если бы они изменились, это, вероятно, имело бы другие последствия, в любом случае, когда это нормально.
@vapcguy: Ну, почти верно. Но есть и другие операции, которые изменят ROWID. Что делать, если вы по какой-то причине экспортируете / импортируете таблицу? Есть и другие операции, но для некоторых из них требуется РАЗРЕШЕНИЕ СТРОКИ. Я просто хочу сказать, что не стоит полагаться на детали реализации, которые могут измениться в будущем.
@ D.Mika Я уверен, что если есть какие-либо операции, в которых ROWID можно изменить, хороший администратор баз данных найдет их и сделает все возможное, чтобы избежать их, если бы существовала вероятность, что они влияют на такую статическую таблицу, как я описал только приложение должно работать. SELECT Вместо этого можно выполнить экспорт таблицы с помощью оператора. Импорт произойдет один раз, а потом никогда больше. Я понимаю, что забота определенно нужна, но проблемы далеко не неизбежны.
Этот синтаксис доступен в Oracle 12c:
^^ Я просто хотел продемонстрировать, что можно использовать строку или строки (во множественном числе) независимо от множества желаемого количества строк.)
select * from some_table извлечь только первую строку; он не работает ни в моем swl devloper, ни в sql plus, поэтому ошибка при выборке.
я действительно не знаю, но когда я открываю его, это выглядит так: SQL * PLus Release 10.1.0.4.2 это не 12 c
В Hibernate 3 есть ли способ сделать эквивалент следующего ограничения MySQL в HQL?
Я не хочу использовать setMaxResults, если это возможно. Это определенно было возможно в более старой версии Hibernate / HQL, но, похоже, оно исчезло.
Я использую Hibernate-5.0.12 . Это все еще не доступно? Было бы очень тяжело получить около миллиона записей, а затем применить к ним фильтр, setMaxResults как заметил @Rachel в ответе @skaffman.
Это было опубликовано на форуме Hibernate несколько лет назад, когда его спросили о том, почему это работает в Hibernate 2, но не в Hibernate 3:
Лимит никогда не был поддерживаемым предложением в HQL. Вы должны использовать setMaxResults ().
Так что, если он работал в Hibernate 2, похоже, что это было случайно, а не по замыслу. Я думаю, что это произошло потому, что Hibernate 2 HQL-анализатор заменит биты запроса, которые он распознал как HQL, и оставит все остальное как есть, чтобы вы могли проникнуть в какой-то нативный SQL. Hibernate 3, однако, имеет правильный синтаксический анализатор AST HQL, и он намного менее прощающий.
Я думаю, что на Query.setMaxResults() самом деле ваш единственный вариант.
Я бы сказал, что подход Hibernate 3 является более правильным. Использование Hibernate подразумевает независимость от базы данных, поэтому вы должны делать такие вещи абстрактно.
Я согласен, но это делает миграцию королевской болью в заднице, когда функции отбрасываются таким образом.
но с setMaxResults, первый запрос выполняется, а затем на наборе результатов, который вы вызываете, setMaxResults который будет принимать ограниченное количество строк результатов из набора результатов и отображать его пользователю, в моем случае у меня есть 3 миллиона записей, которые запрашиваются, а затем вызываю setMaxResults для установки 50 записей, но я не хочу этого делать, хотя сам запрос я хочу запросить 50 записей, есть ли способ сделать это?
@Rachel С setMaxResults Hibernate добавит limit часть к запросу. Он не получит все результаты. Вы можете проверить запрос, который он производит, включив:
Мне это нравится больше всего, потому что setFirstResult на самом деле упоминается в этом ответе, тогда как здесь и в других местах они просто говорят setMaxResults это и setMaxResults то, не упоминая, как установить смещение.
Если вы не хотите использовать setMaxResults() на Query объекте , то вы всегда можете вернуться к использованию нормального SQL.
Я не нахожу HQL захватывающим. Почему бы не написать представление на вашем сервере БД, которое применяет ограничение, а затем заставить HQL взглянуть на это представление: P
Это просто одна из тех вещей, в то время как SQL намного проще, чем HQL для каждого запроса, создание представлений и написание нативного SQL, как правило, не очень хорошо для рефакторинга. Я стараюсь избегать этого, когда могу. Эта реальная проблема заключалась в том, что я все равно неправильно написал свой запрос MySQL и подумал, что setMaxResults странно. Не было
Если вы не хотите использовать setMaxResults, вы также можете использовать Query.scroll вместо list и получить нужные вам строки. Полезно для подкачки, например.
Спасибо, принятый ответ не решил проблему для меня, потому что setMaxResults() сначала загружает каждую запись в памяти, а затем создает подсписок, который при сотнях тысяч или более записей приводит к сбою сервера, потому что ему не хватает памяти. Однако я мог бы перейти от запроса с типом JPA к запросу Hibernate, QueryImpl hibernateQuery = query.unwrap(QueryImpl.class) а затем я мог бы использовать scroll() метод, как вы предложили.
По крайней мере, с диалектом Oracle это не так (Hibernate использует виртуальный столбец ROWNUM). Может быть, это зависит от водителя. Другие БД имеют функцию TOP.
Мой запрос использует выборку соединения. Это приводит к появлению предупреждения Hibernate «firstResult / maxResults, указанное в выборке коллекции; применяется в памяти». Таким образом, используя выборку соединений, Hibernate загружает всю коллекцию в память. Отбрасывание объединения не является вариантом по соображениям производительности. Когда я использую ScrollableResults, у меня больше контроля над тем, какие записи загружаются в память. Я не могу загрузить все записи с одним ScrollableResults, потому что это также приводит к нехватке памяти. Я экспериментирую с загрузкой нескольких страниц с разными ScrollableResults. Если это не работает, я пойду с SQL.
Это странно, я никогда не сталкивался с этим. Да, иногда нужно использовать прямой JDBC, особенно для массовых / пакетных процессов.
Отношения @OneToMany вызывают мои проблемы. Если я каким-то образом смогу выполнить агрегатную функцию Oracle LISTAGG в Hibernate, чтобы объединить несколько значений в одно, то я могу отбросить объединения и заменить их подзапросом.
Вы можете легко использовать нумерацию страниц для этого.
Вы должны пройти, new PageRequest(0, 1) чтобы получить записи и из списка получить первую запись.
Поскольку это очень распространенный вопрос, я написал эту статью , на которой основан этот ответ.
Верх x % строк
Получить первые N строки
MySQL
Настроить
оракул
Преимущество использования setFirstResult и setMaxResults заключается в том, что Hibernate может генерировать синтаксис пагинации для конкретной базы данных для любых поддерживаемых реляционных баз данных.
И вы не ограничены только запросами JPQL. Вы можете использовать setFirstResult и setMaxResults метод семь для собственных запросов SQL.
Короткая альтернатива
В таблице было 10 миллионов записей, сортировка осуществлялась по неиндексированной строке даты и времени:
- План объяснения показал одинаковое значение для всех трех вариантов (323168)
- Но победителем является 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. Новый offset синтаксис имеет тот же план и производительность, что и аналитический подход.
Аналитическое решение только с одним вложенным запросом:
Rank() может быть заменено, Row_Number() но может вернуть больше записей, чем вы ожидаете, если для имени есть повторяющиеся значения.
Я люблю аналитику. Возможно, вы захотите уточнить, в чем разница в поведении между Rank () и Row_Number ().
Действительно, не уверен, почему я не думал о дубликатах. Таким образом, в этом случае, если есть повторяющиеся значения для имени, тогда RANK может дать больше записей, чем вы ожидаете, поэтому вы должны использовать Row_Number.
При упоминании rank() этого также стоит отметить, dense_rank() что может быть более полезным для управления выводом, так как последний не «пропускает» числа, тогда как rank() может. В любом случае для этого вопроса row_number() лучше всего подходит. Еще один не является этот метод применим к любой БД, которая поддерживает упомянутые функции.
В Oracle 12c (см. Предложение по ограничению строк в справочнике по SQL ):
Очевидно, после того, как LIMIT они сошлись со всеми другими поставщиками, чтобы договориться о SQL: 2008, им пришлось взять листок из книги Microsoft и нарушить стандарт.
Интересно, что недавно я слышал, что самый последний стандарт включает этот синтаксис, поэтому, возможно, Oracle перед этим внедрил его. Возможно, это более гибкий, чем LIMIT . OFFSET
@Derek: Да, несоблюдение стандарта вызывает сожаление. Но недавно представленная функциональность в 12cR1 более мощная, чем просто LIMIT n, m (см. Мой ответ). Опять же, Oracle должен был быть реализован LIMIT n, m как синтаксический сахар, как это эквивалентно OFFSET n ROWS FETCH NEXT m ROWS ONLY .
Запросы на нумерацию страниц с упорядочением действительно сложны в Oracle.
Oracle предоставляет псевдостолбец ROWNUM, который возвращает число, указывающее порядок, в котором база данных выбирает строку из таблицы или набора объединенных представлений.
ROWNUM - это псевдоколонка, которая доставляет многим людям неприятности. Значение ROWNUM не всегда назначается строке (это распространенное недоразумение). Это может сбивать с толку, когда значение ROWNUM фактически назначается. Значение ROWNUM присваивается строке после прохождения предикатов фильтра запроса, но до агрегации или сортировки запроса .
Более того, значение ROWNUM увеличивается только после его назначения.
Вот почему следующий запрос не возвращает строк:
Первая строка результата запроса не передает предикат ROWNUM> 1, поэтому ROWNUM не увеличивается до 2. По этой причине никакое значение ROWNUM не будет больше 1, следовательно, запрос не возвращает строк.
Правильно определенный запрос должен выглядеть так:
Узнайте больше о запросах на нумерацию страниц в моих статьях в блоге Vertabelo :
Требуется ограничить число возвращаемых запросом строк. Порядок не имеет значения; подойдут любые n строк.
Пример - функция COUNT включает только значения NOT NUL
Не все это понимают, но функция COUNT будет подсчитывать только те записи, в которых expressions НЕ равно NULL в COUNT( expressions ). Когда expressions является значением NULL, оно не включается в вычисления COUNT. Давайте рассмотрим это дальше.
Есть ли способ заставить Oracle запрос вести себя так, как будто он содержит MySQL limit предложение?
В MySQL , я могу сделать это:
чтобы получить 21-й по 30-й ряды (пропустите первые 20, дайте следующие 10). Строки выбираются после order by , так что это действительно начинается с 20-го имени в алфавитном порядке.
В Oracle , единственное , что люди уже является rownum псевдо-столбец, но он оценивается до order by того , что означает следующее:
вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не то, что я хочу. Это также не позволяет указывать смещение.
@YaroslavShabalin В частности, выгружаемый поиск использует этот паттерн все время. Практически любое приложение с любой функцией поиска будет использовать его. Другим вариантом использования будет загрузка только части длинного списка или клиентской части таблицы и предоставление пользователю возможности расширения.
@YaroslavShabalin Вы не можете получить другой набор результатов, если базовые данные не изменятся из-за ORDER BY . Вот и весь смысл заказа в первую очередь. Если базовые данные изменяются, и ваш набор результатов изменяется из-за этого, то почему бы не показать пользователю обновленные результаты вместо устаревшей информации? Кроме того, государственное управление - это чума, которую следует избегать, насколько это возможно. Это постоянный источник осложнений и ошибок; вот почему функционал становится таким популярным. И когда бы вы знали, чтобы истечь весь набор результатов в памяти? В Интернете у вас нет возможности узнать, когда пользователь уходит.
Начиная с Oracle 12c R1 (12.1), то есть строка ограничение пункт . Он не использует знакомый LIMIT синтаксис, но он может сделать работу лучше с большим количеством опций. Вы можете найти полный синтаксис здесь . (Также читайте больше о том, как это работает внутри Oracle в этом ответе ).
Чтобы ответить на оригинальный вопрос, вот запрос:
(Для более ранних версий Oracle, пожалуйста, обратитесь к другим ответам в этом вопросе)
Помимо нумерации на основе SQL
Разбивка на страницы хороша, когда вы можете индексировать критерии фильтрации и сортировки. Если ваши требования к нумерации страниц подразумевают динамическую фильтрацию, лучше использовать решение с обратным индексом, такое как ElasticSearch.
Собственные запросы SQL
Вам не нужно жестко кодировать нумерацию базы данных при использовании собственных запросов SQL. Hibernate может добавить это к вашим запросам.
Итак, если вы выполняете этот SQL-запрос на PostgreSQL:
Hibernate преобразует его следующим образом:
Синтаксис
Синтаксис для функции COUNT в SQL.
SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];
Или синтаксис для функции COUNT при группировке результатов по одному или нескольким столбцам.
SELECT expression1, expression2, . expression_n,
COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, . expression_n
[ORDER BY expression [ ASC | DESC ]];
Параметры или аргумент
expression1 , expression2 , . expression_n Выражения, которые не инкапсулированы в функции COUNT и должны быть включены в предложение GROUP BY в конце SQL запроса aggregate_expression Это столбец или выражение, чьи ненулевые значения будут учитываться tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица WHERE conditions Необязательный. Это условия, которые должны быть выполнены для выбора записей ORDER BY expression Необязательный. Выражение, используемое для сортировки записей в наборе результатов. Если указано более одного выражения, значения должны быть разделены запятыми ASC Необязательный. ASC сортирует результирующий набор в порядке возрастания по expressions . Это поведение по умолчанию, если модификатор не указан DESC Необязательный. DESC сортирует результирующий набор в порядке убывания по expressions
Вы можете комбинировать смещение с процентами
Вы можете использовать подзапрос для этого как
Посмотрите также тему О ROWNUM и ограничении результатов в Oracle / AskTom для получения дополнительной информации.
Обновление : чтобы ограничить результат нижними и верхними границами, все становится немного более раздутым
(Скопировано из указанной AskTom-статьи)
Обновление 2 : Начиная с Oracle 12c (12.1), доступен синтаксис, ограничивающий строки или начинающийся со смещений.
Смотрите этот ответ для большего количества примеров. Спасибо Крумии за подсказку.
Это, безусловно, способ сделать это, но имейте в виду (как говорится в статье о спросе), производительность запросов снижается по мере увеличения вашего максимального значения. Это хорошее решение для результатов запросов, когда вы хотите видеть только первые несколько страниц, но если вы используете это в качестве механизма для кодирования страниц по всей таблице, вам было бы лучше выполнить рефакторинг кода
+1 Ваша нижняя / верхняя версия фактически помогла мне обойти проблему, когда простое ограниченное сверху предложение rownum резко замедлило мой запрос.
В статье AskTom также есть подсказка оптимизатора, в которой используется SELECT / * + FIRST_ROWS (n) / a. , rownum rnum Перед косой чертой должна стоять звездочка. ТАК чистит это.
Обратите внимание, что для Oracle 11 внешний SELECT с ROWNUM не позволит вам вызвать deleteRow для UpdatableResultSet (с ORA-01446) - ожидая этого изменения 12c R1!
Я провел тестирование производительности для следующих подходов:
Решение
Для ограничения числа возвращаемых строк в Oracle приходится использовать функцию ROWNUM, возвращающую порядковый номер каждой строки результирующего множества (возвращающую, начиная с 1, величину).
Рассмотрим, то происходит при использовании ROWNUM
- Oracle выполняет запрос.
- Oracle извлекает первую строку и называет ее строкой номер 1.
- Номер строки больше 5? Если нет, Oracle возвращает строку, потому что она отвечает критерию: ее порядковый номер меньше или равен 5. Если да, Oracle не возвращает строку.
- Oracle извлекает следующую строку и присваивает ей слудущий порядковый номер по возрастанию (2, затем 3, затем 4 и т.д.).
- Переходим к шгу 3.
Как видно из данного процесса, присвоение значений, возвращаемых функцией ROWNUM, происходит после извлечения очередной строки. Это очень важно и является ключевым моментом. Многие разработчики на Oracle пытаются реализовать извлечение только, скажем, пятой возвращенной запросом строки, задавая ROWNUM = 5. Такое использование условия равенства в сочетании с ROWNUM является неверным. При попытке возвратить пятую строку с помощью ROWNUM = 5 роисходит следующее:
- Oracle выполняет запрос.
- Oracle извлекает первую строку и называет ее строкой номер 1.
- Номер строки равен 5? Если нет, Oracle отбрасывает строку, потому что она не отвечает заданному критерию. Если да, Oracle возвращает строку. Но ответ всегда будет отрицательным!
- Oracle извлекает следующую строку и называет ее строкой номер 1, поскольку первая возвращенная запросом строка должна быть пронумерована как первая строка.
- Переходим к шагу 3.
После тщательного разбора этого процесса становится понятно, почему использование ROWNUM = 5 не обеспечивает возвращения пятой строки. Невозможно получить пятую строку, не возвратив перед этим строки с первой по четвертую!
Однако заметьте, что с помощью ROWNUM = 1 можно получить первую строку. Может показаться, что это противоречит приведенному выше объяснению. Причина, почему ROWNUM = 1 обеспечивает возвращени первой строки, в том, что Oracle для определения наличия строк в таблице приходится извлекать, по крайней мере, одну из них. Внимательно проанализируйте предыдущий процесс, подставив 1 вместо 5, и вы поймете, почему для возвращения одной строки можно в качестве условия задавать ROWNUM = 1.
Tags: Ограничение числа возвращаемых строк
PostgreSQL
аналитический
LimitHandler абстракция
Hibernate LimitHandler определяет логику разбивки на страницы для конкретной базы данных, и, как показано на следующей диаграмме, Hibernate поддерживает множество опций разбивки на базы данных:
Теперь, в зависимости от используемой вами системы реляционной базы данных, вышеприведенный запрос JPQL будет использовать правильный синтаксис разбиения на страницы.
Что в таблице?
SQL Server
Читайте также: