Oracle ограничить количество строк в выборке
есть ли способ сделать Oracle запрос ведет себя так, как будто он содержит MySQL limit предложения?
на MySQL , Я могу сделать это:
чтобы получить 21-й до 30-й строк (пропустите первые 20, дайте следующие 10). Строки выбираются после order by , поэтому он действительно начинается с 20-го имени в алфавитном порядке.
на Oracle , единственное, что люди упоминают это rownum псевдо-столбец, но он оценивается до order by , что означает это:
вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не то, что я хочу. Он также не позволяет указать смещение.
начиная с Oracle 12c R1 (12.1), там is a строка ограничительного пункта. Он не использует знакомые LIMIT синтаксис, но он может сделать работу лучше, с большим количеством опций. Вы можете найти полный синтаксис здесь.
чтобы ответить на исходный вопрос, вот запрос:
(для более ранних версий Oracle, пожалуйста, обратитесь к другим ответам в этом вопрос)
примеры:
следующие примеры были приведены из ссылке, в надежде предотвратить гниение ссылке.
настройка
что в таблице?
первый N строки
первый N строк если N th строка имеет связи, получить все связанные строки
Top x % от строки
используя смещение, очень полезно для разбиения на страницы
вы можете комбинировать смещение с процентами
вы можете использовать подзапрос для этого, как
Также посмотрите на тему на ROWNUM и ограничивающие результаты в Oracle/AskTom для получения дополнительной информации.
обновление: Чтобы ограничить результат как нижними, так и верхними границами, вещи становятся немного более раздутыми с
(скопировано с указанного AskTom-article)
обновление 2: Начиная с Oracle 12c (12.1) существует синтаксис, доступный для ограничьте строки или начните с смещений.
посмотреть ответ для получения дополнительных примеров. Благодаря Krumia за подсказку.
Я провел тестирование производительности для следующих подходов:
Asktom
аналитическая
Короткое Альтернатива
таблица имела 10 миллионов записей, сортировка была на неиндексированной строке datetime:
- объяснить план показал одинаковое значение для всех трех вариантов (323168)
- но победителем является AskTom (с аналитическим следующим закрытием сзади)
Выбор первых 10 строк взял:
выбор строк между 100,000 и 100,010:
- AskTom: 60 секунд
- аналитический: 100 секунд
выбор строк между 9,000,000 и 9,000,010:
- AskTom: 130 секунды!--17-->
- аналитический: 150 секунд
аналитическое решение с одним вложенным запросом:
Rank() можно заменить на Row_Number() но может вернуть больше записей, чем вы ожидаете, если есть повторяющиеся значения для имени.
Я хочу использовать синтаксис Oracle, чтобы выбрать только 1 строку из таблицы DUAL . Например, я хочу выполнить такой запрос:
. и было бы около 40 записей. Но мне нужна только одна запись. . И я хочу, чтобы это произошло без WHERE оговорок.
Мне нужно что-то в поле table_name, например:
Какая версия Oracle? Использование ROWNUM или ROW_NUMBER (9i +) означало бы необходимость предложения WHERE Вы действительно пробовали бежать select user from dual ? Если нет, попробуйте это и посмотрите, что у вас получится. В стандартной системе Oracle вы вернетесь пользователя, с которым выполняете команду.Вы используете ROWNUM.
@ypercube, насколько я могу судить, это так. (По крайней мере, это работает для моей установки oracle10g.) @bdares: будет работать, да. Но не ваш ответ с расширением order by . Да. ROWNUM - это специальный столбец, который добавляется к набору результатов и перечисляет результаты. Вы также можете использовать его, чтобы выбрать несколько, например, если вы хотите найти 10 самых высокооплачиваемых сотрудников, вы можете сказать: «ВЫБРАТЬ пользователя ИЗ СОТРУДНИКОВ, ГДЕ ROWNUM <= 10 ЗАКАЗАТЬ ПО УБЫТКУ ЗАПЛАТЫ» Вам понадобится: SELECT * FROM (SELECT user FROM Employees ORDER BY SALARY DESC) WHERE ROWNUM <= 10Я нашел это "решение" спрятанным в одном из комментариев. Поскольку я некоторое время искал это, я хотел бы немного выделить его (пока не могу комментировать или делать такие вещи . ), поэтому я использовал следующее:
Это напечатает мне желаемую запись [Столбец] из самой новой записи в таблице, предполагая, что [Дата] всегда вставляется через SYSDATE.
Я обнаружил, что это также будет работать, если вы сделаете заказ ROWID , если вы никогда не удаляете какие-либо записи и всегда заботитесь о последней вставленной / измененной. @vapcguy: Не ждите, что ROWID будет заказан, даже если вы никогда не удаляете строку из таблицы! Даже если это сработает для вас сейчас, никогда не будет гарантировано работать в будущих версиях. @ D.Mika На самом деле, если он работает сейчас, и вы никогда не добавляете / не удаляете / не обновляете / не удаляете записи, проблем быть не должно. Записи могут быть изменены только в том случае, если вы действительно их измените. Существует это заблуждение, которое каким-то образом ROWID случайно модифицируется Oracle. Это не так. Он основан на фактическом изменении строк, то есть вы удаляете одну, а затем вставляете ее. Вставленный получит старый ROWID . Есть такие вещи, как статические таблицы, которые никогда не обновляются, как в США, что является хорошим примером. Если бы они изменились, это, вероятно, имело бы другие последствия, в любом случае, когда это нормально. @vapcguy: Ну, почти верно. Но есть и другие операции, которые изменят ROWID. Что делать, если вы по какой-то причине экспортируете / импортируете таблицу? Есть и другие операции, но для некоторых из них требуется РАЗРЕШЕНИЕ СТРОКИ. Я просто хочу сказать, что не стоит полагаться на детали реализации, которые могут измениться в будущем. @ D.Mika Я уверен, что если есть какие-либо операции, в которых ROWID можно изменить, хороший администратор баз данных найдет их и сделает все возможное, чтобы избежать их, если бы существовала вероятность, что они влияют на такую статическую таблицу, как я описал только приложение должно работать. SELECT Вместо этого можно выполнить экспорт таблицы с помощью оператора. Импорт произойдет один раз, а потом никогда больше. Я понимаю, что забота определенно нужна, но проблемы далеко не неизбежны.Этот синтаксис доступен в Oracle 12c:
^^ Я просто хотел продемонстрировать, что можно использовать строку или строки (во множественном числе) независимо от множества желаемого количества строк.)
Есть ли способ заставить 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, пожалуйста, обратитесь к другим ответам в этом вопросе)
Примеры:
Следующие примеры были процитированы со ссылочной страницы в надежде предотвратить гниение ссылок.
Настроить
Что в таблице?
Получить первые N строки
Получить первые N строки, если N й строки имеет связи, получить все связанные строки
Верх x % строк
Использование смещения, очень полезно для нумерации страниц
Вы можете комбинировать смещение с процентами
Просто для расширения: OFFSET FETCH синтаксис является синтаксическим сахаром. ПодробностиВы можете использовать подзапрос для этого как
Посмотрите также тему О 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!Я провел тестирование производительности для следующих подходов:
Asktom
аналитический
Короткая альтернатива
В таблице было 10 миллионов записей, сортировка осуществлялась по неиндексированной строке даты и времени:
- План объяснения показал одинаковое значение для всех трех вариантов (323168)
- Но победителем является AskTom (с аналитическим следом за ним)
Выбор первых 10 строк занял:
Выбор строк от 100 000 до 100 010:
- AskTom: 60 секунд
- Аналитический: 100 секунд
Выбор строк между 9 000 000 и 9 000 010:
- AskTom: 130 секунд
- Аналитический: 150 секунд
Аналитическое решение только с одним вложенным запросом:
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 :
Узнайте, как ограничить набор результатов SQL-запроса верхними N строками только при использовании систем реляционных баз данных Oracle, SQL Server, PostgreSQL или MySQL.
Вступление
В этой статье мы рассмотрим, как мы можем ограничить набор результатов SQL-запроса только верхними N строками.
Ограничение набора результатов SQL очень важно, когда базовый запрос может привести к получению очень большого количества записей, что может оказать существенное влияние на производительность приложения .
Зачем ограничивать количество строк SQL-запроса?
Извлечение большего количества данных, чем необходимо, является основной причиной проблем с производительностью доступа к данным. При разработке конкретного бизнес-варианта использования объем данных, доступных как в среде разработки, так и в среде контроля качества, довольно мал, поэтому не все SQL-запросы записываются таким образом, чтобы результирующий набор ограничивался фиксированным количеством записей.
После развертывания приложения в рабочей среде данные начинают накапливаться, и запросы, которые когда-то были очень быстрыми, начинают выполняться все медленнее и медленнее. Даже если индексы применяются к критериям фильтрации и сортировки SQL-запросов, если индекс не охватывает весь запрос (например, сканирование только по индексу), записи таблицы должны быть проверены с использованием шаблона чтения с произвольным доступом.
Если размер результирующего набора невелик и база данных может использовать индекс для критериев фильтрации и сортировки, то затраты, связанные с чтением записей таблицы, все равно меньше, чем сканирование всей таблицы. С другой стороны, если размер результирующего набора очень велик и базе данных требуется доступ к очень большому проценту данной таблицы, то использование индекса будет менее эффективным, чем сканирование всей таблицы.
Чтобы доказать это, рассмотрим, что у нас есть следующая таблица post в нашей базе данных, которая содержит 5000 записей:
Итак, если мы не ограничим набор результатов верхними N записями:
Обратите внимание на последовательное сканирование всех 5000 строк таблицы post .
Теперь при добавлении предложения LIMIT, которое ограничивает набор результатов только 5 записями:
План выполнения SQL-запроса Top-N выглядит следующим образом:
Обратите внимание, что на этот раз было использовано сканирование индекса, и только 5 записей были отсканированы и извлечены. Более того, время выполнения в сотни раз меньше, чем при предыдущем выполнении.
Размер набора результатов SQL-запроса может повлиять на план выполнения, поскольку база данных может выбрать сканирование всей таблицы, даже если для критериев фильтрации и сортировки запросов доступен индекс.
Не только план выполнения может быть менее эффективным, но и извлечение большего количества данных, чем необходимо, потребует значительного объема ресурсов как на стороне базы данных, сервера, так и на стороне клиента.
Во-первых, записи должны быть извлечены в пул буферов базы данных.
После этого записи отправляются по сети на сервер. На сервере драйвер JDBC выделит все необходимые объекты Java для представления набора результатов запроса.
Однако, поскольку JDBC Набор результатов не передается клиенту, записи должны быть преобразованы в сущности или DTOs .
Полученные объекты или DTO могут быть преобразованы в JSON и снова переданы по сети клиенту, где объекты JSON должны быть загружены в память браузера перед использованием для визуализации пользовательского интерфейса.
Для извлечения больших объемов данных требуется значительное количество ресурсов на нескольких уровнях (например, база данных, сервер, клиент).
Выборка только верхних N строк
Таким образом, поскольку дисплей пользовательского интерфейса имеет ограниченный размер, нет смысла извлекать больше данных, чем может быть отображено одновременно. Теперь, в зависимости от используемой вами базовой системы реляционных баз данных, предложение SQL, позволяющее ограничить размер набора результатов запроса, может отличаться.
SQL:Стандарт 2008
До SQL:2008 не существовало стандартного способа извлечения верхних N записей из заданного набора результатов. Стандартный синтаксис выглядит следующим образом:
Обратите внимание на предложение ИЗВЛЕКАТЬ ТОЛЬКО ПЕРВЫЕ 5 СТРОК , которое сообщает базе данных, что мы заинтересованы в извлечении только первых 5 записей. Еще одна вещь, на которую следует обратить внимание, заключается в том, что мы используем предложение ORDER BY , поскольку в противном случае нет гарантии, какие записи будут первыми включены в возвращаемый набор результатов.
Предложение SQL:2008 Top-N записей поддерживается в Oracle с 12c, SQL Server с 2012 года и PostgreSQL с 8.4.
SQL Server
Хотя SQL Server поддерживает стандартный синтаксис SQL:2008 Top-N, вам также необходимо указать предложение OFFSET:
Поскольку нас интересуют только записи Top-N, в нашем случае СМЕЩЕНИЕ равно 0.
До SQL Server 2012 вам приходилось использовать TOP для ограничения размера результирующего набора:
Oracle 11g и более старые версии
До версии 12c для извлечения записей верхнего уровня N вам приходилось использовать производную таблицу и псевдоколонку ROWNUM :
Причина, по которой используется внешняя производная таблица, заключается в том, что значение псевдоколонки ROWNUM присваивается до выполнения предложения ORDER BY. Используя производную таблицу, мы можем убедиться, что псевдоколоночка ROWNUM, которую мы используем для фильтрации записей Top-N, назначена после сортировки базового набора результатов.
MySQL и PostgreSQL 8.3 или старше
Традиционно MySQL и PostgreSQL используют предложение LIMIT для ограничения набора результатов записями Top-N:
Вывод
Получение нужного объема данных очень важно для производительности приложения. К счастью, SQL позволяет нам ограничить данный запрос записями Top-N, используя либо стандартный синтаксис SQL:2008, либо альтернативы для конкретной базы данных.
Читайте также: