Лучшая практика для разбивки на страницы в Oracle?

Проблема: мне нужно написать хранимую процедуру (процедуры), которая вернет результирующий набор одной страницы из строк и число полных строк.

Решение A: Я создаю две хранимые процедуры, которые возвращают набор результатов для одной страницы, а другой, который возвращает скалярные полные строки. В плане объяснения говорится, что первый sproc имеет стоимость 9, а второй - 3.

SELECT  *
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC ) AS RowNum, ...
        ) AS PageResult
WHERE   RowNum >= @from
    AND RowNum < @to
ORDER BY RowNum

SELECT  COUNT(*)
FROM    ...

Решение B: Я помещаю все в один sproc, добавляя то же число TotalRows к каждой строке в наборе результатов. Это решение кажется хакерским, но имеет стоимость 9 и только один sproc, поэтому я склонен использовать это решение.

SELECT * 
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC  ) RowNum, COUNT(*) OVER () TotalRows,
WHERE RowNum >= from
        AND RowNum < to
ORDER BY RowNum;

Есть ли передовая практика для разбивки на страницы в Oracle? Какое из вышеупомянутых решений наиболее часто используется на практике? Кто-нибудь из них считается просто неправильным? Обратите внимание, что моя БД является и останется относительно небольшой (менее 10 ГБ).

Я использую Oracle 11g и последний ODP.NET с VS2010 SP1 и Entity Framework 4.4. Мне нужно окончательное решение для работы в EF 4.4. Я уверен, что есть, вероятно, лучшие методы для разбивки на страницы в целом, но мне нужно, чтобы они работали с EF.

Ответ 1

Если вы уже используете аналитику (ROW_NUMBER() OVER...), то добавление еще одной аналитической функции в том же разделе приведет к незначительной стоимости запроса.

С другой стороны, есть много других способов сделать нумерацию страниц, один из них использует rownum:

SELECT * 
  FROM (SELECT A.*, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col) A
         WHERE rownum <= :Y)
 WHERE rn >= :X

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

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

Ответ 2

Это может помочь:

   SELECT * FROM 
     ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp)
     WHERE Row_Num BETWEEN 5 and 10;

Ответ 3

В Oracle 12C вы можете использовать лимит LIMIT и OFFSET для нумерации страниц.

Пример. Предположим, у вас есть tab Таблица tab из которой необходимо извлечь данные на основе столбца типа данных DATE dt в порядке убывания с разбивкой на страницы.

page_size:=5

select * from tab
order by dt desc
OFFSET nvl(page_no-1,1)*page_size ROWS FETCH NEXT page_size ROWS ONLY;

Объяснение:

page_no = 1 page_size = 5

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY - OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY 5 строк

page_no = 2 page_size = 5

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY - OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY следующие 5 строк

и так далее.

Референс-страницы -

https://dba-presents.com/index.php/databases/oracle/31-new-pagination-method-in-oracle-12c-offset-fetch

https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging

Ответ 4

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

select * from ( select * from "table" order by "column" desc ) where ROWNUM > 0 and ROWNUM <= 5;

Ответ 5

Извините, это работает с сортировкой:

SELECT * FROM (SELECT ROWNUM rnum,a.* FROM (SELECT * FROM "tabla" order by "column" asc) a) WHERE rnum BETWEEN "firstrange" AND "lastrange";

Ответ 6

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

Добавьте в таблицу новый столбец ROW_NUMBER.
Сделать столбец первичным ключом или добавить на него уникальный индекс.
Используйте программу народонаселения (в моем случае Informatica), чтобы заполнить столбец rownum.
Извлечь записи из таблицы с помощью инструкции. (SELECT * FROM TABLE WHERE ROW_NUMBER МЕЖДУ LOWER_RANGE И UPPER_RANGE).

Этот метод эффективен, если нам нужно сделать безусловную выборку разбиения на огромную таблицу.

Ответ 7

Чистый способ организовать ваш код SQL может быть с помощью оператора WITH.

В сокращенной версии реализовано также общее количество результатов и общее количество страниц.

Например

WITH SELECTION AS (
    SELECT FIELDA, FIELDB, FIELDC FROM TABLE), 
NUMBERED AS (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY FIELDA) RN, 
    SELECTION.*
    FROM SELECTION)
SELECT
    (SELECT COUNT(*) FROM NUMBERED) TOTAL_ROWS,
    NUMBERED.*
FROM NUMBERED
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

Этот код дает вам постраничный набор результатов с еще двумя полями:

TOTAL_ROWS с полными строками вашего полного SELECTION
RN номер строки записи

Требуется 2 параметра:: :page_size и :page_number чтобы :page_number ваш SELECTION

Уменьшенная версия

Выбор реализует уже ROW_NUMBER()

WITH SELECTION AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY FIELDA) RN,
        FIELDA, 
        FIELDB, 
        FIELDC 
    FROM TABLE) 
SELECT
    :page_number PAGE_NUMBER,
    CEIL((SELECT COUNT(*) FROM SELECTION ) / :page_size) TOTAL_PAGES,
    :page_size PAGE_SIZE,
    (SELECT COUNT(*) FROM SELECTION ) TOTAL_ROWS,
    SELECTION.*
FROM SELECTION 
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)