Пейджинг с Oracle

Примерно так должно работать: Из блога Франса Боумы

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

Спросите Тома о разбиении на страницы и очень, очень полезных аналитических функциях.

Это выдержка с той страницы:

select * from (
    select /*+ first_rows(25) */
     object_id,object_name,
     row_number() over
    (order by object_id) rn
    from all_objects
)
where rn between :n and :m
order by rn;

Попробуйте следующее:

SELECT *
FROM
  (SELECT FIELDA,
    FIELDB,
    FIELDC,
    ROW_NUMBER() OVER (ORDER BY FIELDC) R
  FROM TABLE_NAME
  WHERE FIELDA = 10
  )
WHERE R >= 10
AND R   <= 15;

via [tecnicume]

В интересах полноты, для людей, ищущих более современное решение, в Оракул 12c есть некоторые новые функции, включая улучшенное разбиение по страницам и верхнюю обработку.

Пейджинг

Пейджинг выглядит так:

SELECT *
FROM user
ORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

Лучшие записи N

Получение топовых записей выглядит так:

SELECT *
FROM user
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY

Обратите внимание, что в обоих приведенных выше примерах запросов есть предложения ORDER BY. Новые команды соблюдают их и запускаются на отсортированных данных.

Мне не удалось найти хорошую справочную страницу Oracle для FETCH или OFFSET, но в эта страница есть отличный обзор этих новых функций.

Представление

Как отмечает @wweicker в комментариях ниже, производительность - это проблема с новым синтаксисом в 12c. У меня не было копии 18c, чтобы проверить, улучшил ли ее Oracle с тех пор.

Интересно, что мои фактические результаты были возвращены немного быстрее, когда я впервые выполнил запросы к своей таблице (более 113 миллионов строк) для нового метода:

• Новый метод: 0,013 секунды.
• Старый метод: 0,107 секунды.

Однако, как упоминал @wweicker, план объяснения выглядит намного хуже для нового метода:

• Стоимость нового метода: 300 110
• Стоимость старого метода: 30

Новый синтаксис вызвал полное сканирование индекса в моем столбце, что было полной стоимостью. Скорее всего, при ограничении неиндексированных данных все становится намного хуже.

Давайте посмотрим, включает ли один неиндексированный столбец в предыдущий набор данных:

• Время / стоимость нового метода: 189,55 секунды / 998 908
• Время / стоимость старого метода: 1,973 секунды / 256

Резюме: используйте с осторожностью, пока Oracle не улучшит эту обработку. Если у вас есть индекс для работы, возможно, вам удастся использовать новый метод.

Надеюсь, у меня скоро будет копия 18c, с которой можно будет поиграть, и я смогу обновить

Сразу хочу подытожить ответы и комментарии. Есть несколько способов разбивки на страницы.

До oracle 12c не было функциональности OFFSET / FETCH, поэтому взгляните на белая бумага, как предлагал @jasonk. Это самая полная статья о различных методах, которую я нашел, с подробным объяснением преимуществ и недостатков. Чтобы скопировать их сюда, потребуется много времени, поэтому я не буду этого делать.

Также есть хорошая статья от создателей jooq, объясняющая некоторые общие предостережения при разбивке на страницы Oracle и других баз данных. сообщение в блоге jooq

Хорошие новости, начиная с oracle 12c у нас появилась новая функциональность OFFSET / FETCH. Новые возможности OracleMagazine 12c. Пожалуйста, обратитесь к разделу «Топ-N запросов и разбивки на страницы».

Вы можете проверить свою версию оракула, выполнив следующее заявление

SELECT * FROM V$VERSION

В своем проекте я использовал Oracle 12c и Java. Код подкачки выглядит так:

 public public List<Map<String, Object>> getAllProductOfferWithPagination(int pageNo, int pageElementSize, Long productOfferId, String productOfferName) {
    try {

        if (pageNo==1){
            //do nothing
        } else{
            pageNo=(pageNo-1)*pageElementSize+1;
        }
        System.out.println("algo pageNo: " + pageNo +"  pageElementSize: "+ pageElementSize+"  productOfferId: "+ productOfferId+"  productOfferName: "+ productOfferName);

        String sql = "SELECT * FROM ( SELECT * FROM product_offer po WHERE po.deleted=0 AND (po.product_offer_id=? OR po.product_offer_name LIKE ? )" +
             " ORDER BY po.PRODUCT_OFFER_ID asc) foo OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ";

       return jdbcTemplate.queryForList(sql,new Object[] {productOfferId,"%"+productOfferName+"%",pageNo-1, pageElementSize});

    } catch (Exception e) {
        System.out.println(e);
        e.printStackTrace();
        return null;
    }