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

У меня есть SQL-запрос, который выглядит примерно так:

SELECT * FROM(
    SELECT
        ...,
        row_number() OVER(ORDER BY ID) rn
    FROM
        ...
) WHERE rn between :start and :end

По сути, это часть ORDER BY, которая замедляет работу. Если бы я его удалял, стоимость EXPLAIN снижалась на порядок (более 1000x). Я пробовал это:

SELECT 
    ...
FROM
    ...
WHERE
    rownum between :start and :end

Но это не дает правильных результатов. Есть ли простой способ ускорить это? Или мне придется потратить больше времени на инструмент EXPLAIN?

Ответ 1

ROW_NUMBER довольно неэффективен в Oracle.

См. статью в своем блоге для подробностей о производительности:

Oracle: ROW_NUMBER vs 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 в подзапрос.

Ответ 2

Похож на запрос страницы.

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

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

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

Ответ 3

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

Ответ 4

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

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

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

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

Ответ 5

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

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