Как я могу ускорить row_number в Oracle?

ROW_NUMBER совершенно неэффективен в Oracle.

Подробнее о производительности см. в статье в моем блоге:

• Oracle: ROW_NUMBER и ROWNUM

Для вашего конкретного запроса я бы рекомендовал вам заменить его на ROWNUM и убедиться, что используется индекс:

SELECT  *
FROM    (
        SELECT  /*+ INDEX_ASC(t index_on_column) NOPARALLEL_INDEX(t index_on_column) */
                t.*, ROWNUM AS rn
        FROM    table t
        ORDER BY
                column
        )
WHERE rn >= :start
      AND rownum <= :end - :start + 1

Этот запрос будет использовать COUNT STOPKEY

Также либо убедитесь, что column не допускает значение NULL, либо добавьте условие WHERE column IS NOT NULL.

В противном случае индекс нельзя будет использовать для извлечения всех значений.

Обратите внимание, что вы не можете использовать ROWNUM BETWEEN :start and :end без подзапроса.

ROWNUM всегда назначается последним и проверяется последним, поэтому ROWNUM всегда идут по порядку без пропусков.

Если вы используете ROWNUM BETWEEN 10 and 20, первая строка, удовлетворяющая всем остальным условиям, станет кандидатом на возврат, временно получит ROWNUM = 1 и не пройдет тест ROWNUM BETWEEN 10 AND 20.

Тогда следующая строка будет кандидатом, назначенным с ROWNUM = 1 и неудачным, и т. д., поэтому, наконец, строки не будут возвращены вообще.

Это можно обойти, поместив ROWNUM в подзапрос.

Для меня это похоже на запрос на разбивку на страницы.

Из этой статьи ASKTOM (около 90% вниз по странице):

Вам нужно заказать что-то уникальное для этих запросы на разбивку на страницы, чтобы ROW_NUMBER каждый раз детерминировано присваивался строкам.

Кроме того, ваши запросы далеко не одинаковы, поэтому я не уверен, в чем преимущество сравнения затрат одного с другим.

Проиндексирован ли ваш столбец ORDER BY? Если нет, то это хорошее место для начала.

Часть проблемы заключается в том, насколько велик промежуток между «началом» и «концом» и где они «живут». Допустим, у вас есть миллион строк в таблице, и вам нужны строки с 567 890 по 567 900, тогда вам придется смириться с тем фактом, что вам нужно будет просмотреть всю таблицу, почти все это отсортировать по идентификатору, и определите, какие строки попадают в этот диапазон.

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

Это также не то, с чем может помочь индекс. Индекс задает порядок, но в лучшем случае это дает вам возможность с чего-то начать, а затем вы продолжаете чтение, пока не дойдете до 567 900-й записи.

Если вы показываете конечному пользователю по 10 элементов за раз, возможно, стоит взять первые 100 из БД, а затем приложение разбивает эти 100 на десять фрагментов.

Проводите больше времени с инструментом EXPLAIN PLAN. Если вы видите TABLE SCAN, вам нужно изменить свой запрос.

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