FETCH NEXT 5 ROWS ONLY Влияние на производительность предложения Select, которое возвращает 50 миллионов записей

Да, использование пагинации набора ключей, скорее всего, поможет вам избежать ошибки ORA-01652, поскольку для получения первых 5000 строк Oracle не требует одновременной загрузки и сортировки всех 50 миллионов строк. Вместо этого он извлекает строки меньшими порциями на основе указанного вами ключа (например, столбца идентификатора), что уменьшает необходимый объем временного хранилища (пространства TEMP).

Вот почему это работает лучше:

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

Пагинация не поможет.

Вы получаете ORA-01652, потому что ваш ORDER BY использует рабочую область, которая перешла из PGA во временное пространство, а ваше табличное пространство TEMP недостаточно велико, чтобы вместить временные сегменты, необходимые для завершения сортировки. 50 миллионов строк и все выбранные столбцы означают очень большие временные сегменты.

Чтобы решить проблему, вы можете выполнить одно из следующих действий:

1. Попросите вашего администратора базы данных увеличить табличное пространство TEMP (разумный запрос).
2. Значительно увеличьте использование PGA (с помощью pga_aggregate_target, если v$pgastat.global memory bound < 1 ГБ) или установив политику рабочей области вручную и определив sort_area_size самостоятельно, но это не рекомендуемый метод, поскольку вы можете легко использовать слишком много памяти).
3. Вы можете переопределить таблицу как IOT (таблица, организованная по индексу), указав свой PK в столбце, по которому вы хотите упорядочить. Тогда ORDER BY должен выполнить сканирование по возрастанию индекса вообще без какой-либо операции сортировки. Обратной стороной является то, что это сканирование индекса, что означает чтение одного блока для следования связанному списку от листового блока к листовому блоку, поэтому оно менее эффективно, чем прямое чтение по пути всего сегмента.
4. Создание обычного индекса для обычной таблицы-кучи также позволяет избежать сортировки, но она будет работать очень плохо, поскольку каждая строка потребует отдельного чтения блока в сегменте таблицы, а при 50 миллионах строк это будет слишком много. На этом объеме вам потребуется выполнить полное сканирование таблицы, и, поскольку вам нужна операция сортировки, вы захотите, чтобы она была распараллелена.
5. Убедитесь, что вы используете параллелизм (используйте подсказку parallel с соответствующим DOP). При работоспособном DOP каждый подчиненный PX получает собственное распределение PGA (до 1 ГБ каждый при автоматической политике и 2 ГБ при ручной политике), поэтому вы можете использовать гораздо больше памяти PGA для сортировки (и, следовательно, меньше временного пространства), чем при последовательном процессе. .
6. Вы можете выполнить собственную «пагинацию», используя предикат WHERE, который собирает только часть необходимых данных и продвигает их программно. Это уменьшает количество входных данных в операцию сортировки и, следовательно, может быть адаптировано к доступному пространству TEMP, но это очень неэффективно, поскольку вам придется повторно сканировать таблицу при каждом запросе. Всегда лучше взять все необходимое за один раз. Хотя вы можете удалить это повторное сканирование с помощью индекса, вы вернетесь к неэффективности, упомянутой выше в пункте 4.
7. Уменьшите количество извлекаемых столбцов. Вам действительно нужны они все? Любое сокращение столбцов приведет к уменьшению размера временного сегмента, необходимого для сортировки.
8. Вы также можете вообще исключить ORDER BY. Вам действительно нужны отсортированные результаты? Большинство программных фрагментов этого не делают.

Все вышеизложенное вам следует обсудить со своим администратором базы данных, который должен знать, сколько доступной памяти на хосте(ах) базы данных для PGA, сколько места доступно для расширения TEMP, какую доступность ЦП можно предложить. соответствующий уровень параллелизма (DOP) и т. д.

Лучше запускать код рядом с данными, а затем запускать данные в коде. Как вы испытали. Java не подходит для манипулирования данными. База данных и SQL есть.

Сказал, что: «FETCH NEXT 5000 ROWS ONLY» приведет к снижению производительности на каждой странице. Например. первые несколько страниц будут быстро светиться. Несколько последних будут загружаться навсегда. Это связано с тем, что механизму базы данных потребуется прочитать 10 000 000 строк, чтобы вернуть строки между 10 000 000 и 10 005 000. Каждый ввод-вывод является дорогостоящим. Вот почему это должно иметь значение.

Стабильную производительность можно получить с помощью правильной индексации и предложения WHERE. Лучшее объяснение, которое я нашел: https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way. Пример предназначен для PostgreSQL, но будет работать с любой базой данных.