Подтвердить что ты не робот

(Oracle) Как получить общее количество результатов при использовании запроса разбивки на страницы?

Я использую Oracle 10g и следующую парадигму, чтобы получить страницу из 15 результатов за время (так что, когда пользователь смотрит на страницу 2 результата поиска, они видят записи 16-30).

select * 
  from 
( select rownum rnum, a.*
    from (my_query) a
   where rownum <= 30 )
where rnum > 15;

Сейчас мне нужно запустить отдельный оператор SQL, чтобы сделать "select count" на "my_query", чтобы получить общее количество результатов для my_query (чтобы я мог показать его пользователю и использовать его для определения общего количества страниц и т.д.).

Есть ли способ получить общее количество результатов, не делая этого через второй запрос, т.е. получая его из запроса выше? Я попытался добавить "max (rownum)", но он не работает (я получаю сообщение об ошибке [ORA-01747], которое, похоже, указывает, что мне не нравится, что у меня есть ключевое слово rownum в группе).

Мое обоснование желания получить это из исходного запроса, а не делать его в отдельной инструкции SQL, заключается в том, что "my_query" - это дорогостоящий запрос, поэтому я бы предпочел не запускать его дважды (один раз, чтобы получить счет, и один раз чтобы получить страницу данных), если мне это не нужно; но независимо от того, какое решение я могу придумать, чтобы получить количество результатов из одного запроса (и в то же время получить нужную мне страницу данных), не следует добавлять много, если есть дополнительные дополнительные накладные расходы, если это возможно. Просьба сообщить.

Вот именно то, что я пытаюсь сделать, для которого я получаю ошибку ORA-01747, потому что я считаю, что мне не нравится, что у меня есть ROWNUM в группе. Примечание. Если есть другое решение, которое не использует max (ROWNUM), но что-то еще, это тоже прекрасно. Это решение было моей первой мыслью о том, что может сработать.

 SELECT * FROM (SELECT r.*, ROWNUM RNUM, max(ROWNUM)
 FROM (SELECT t0.ABC_SEQ_ID AS c0, t0.FIRST_NAME, t0.LAST_NAME, t1.SCORE
 FROM ABC t0, XYZ t1
 WHERE (t0.XYZ_ID = 751) AND 
 t0.XYZ_ID = t1.XYZ_ID 
 ORDER BY t0.RANK ASC) r WHERE ROWNUM <= 30 GROUP BY r.*, ROWNUM) WHERE RNUM > 15

--------- EDIT -------- Примечание. Основываясь на первом комментарии, я попробовал следующее, которое, похоже, работает. Я не знаю, насколько хорошо он работает в сравнении с другими решениями (я ищу решение, которое удовлетворяет моему требованию, но делает все возможное). Например, когда я запускаю это, это занимает 16 секунд. Когда я вынимаю COUNT (*) OVER() RESULT_COUNT, это занимает всего 7 секунд:

    SELECT * FROM (SELECT r.*, ROWNUM RNUM, ) 
    FROM (SELECT COUNT(*) OVER () RESULT_COUNT, 
          t0.ABC_SEQ_ID AS c0, t0.FIRST_NAME, t1.SCORE 
    FROM ABC t0, XYZ t1 
    WHERE (t0.XYZ_ID = 751) AND t0.XYZ_ID = t1.XYZ_ID 
    ORDER BY t0.RANK ASC) r WHERE ROWNUM <= 30) WHERE RNUM > 1

План объяснения изменяется от выполнения SORT (ORDER BY STOP KEY), чтобы сделать ОКНО (SORT).

До:

SELECT STATEMENT () 
 COUNT (STOPKEY)    
  VIEW ()   
   SORT (ORDER BY STOPKEY)  
    NESTED LOOPS () 
     TABLE ACCESS (BY INDEX ROWID)  XYZ
      INDEX (UNIQUE SCAN)   XYZ_ID
     TABLE ACCESS (FULL)    ABC

После:

SELECT STATEMENT () 
 COUNT (STOPKEY)    
  VIEW ()   
   WINDOW (SORT)    
    NESTED LOOPS () 
     TABLE ACCESS (BY INDEX ROWID)  XYZ
      INDEX (UNIQUE SCAN)   XYZ_ID
     TABLE ACCESS (FULL)    ABC
4b9b3361

ОТВЕТЫ

Ответ 1

Я думаю, вам нужно изменить свой запрос на нечто подобное, чтобы получить всю необходимую информацию по "одиночному" запросу.

SELECT *
FROM (SELECT r.*, ROWNUM RNUM, COUNT(*) OVER () RESULT_COUNT 
      FROM (SELECT t0.ABC_SEQ_ID AS c0, t0.FIRST_NAME, t1.SCORE
            FROM ABC t0, XYZ t1
            WHERE (t0.XYZ_ID = 751) 
            AND t0.XYZ_ID = t1.XYZ_ID 
            ORDER BY t0.RANK ASC) R)
WHERE RNUM between 1 and 15

Причина в том, что функция окна COUNT(*) OVER() оценивается после предложения WHERE, поэтому не дает общего количества записей, а количество записей, удовлетворяющих условию ROWNUM <= 30.

Если вы не можете принять производительность этого запроса или выполнить 2 отдельных запроса, возможно, вам стоит подумать о решении, например, предложенном FrustratedWithFormsDesigner в его комментарии о кэшировании количества записей.

Если вы регулярно работаете с базами данных, я рекомендую вам получить копию SQL Cookbook. Это исключительная книга с множеством полезных советов.

Ответ 2

Это работает?

select * 
  from 
( select rownum rnum, a.*, b.total
    from (my_query) a,   (select count(*) over () total from my_query) b
   where rownum <= 30 )
where rnum > 15;

Ответ 3

WITH
base AS
(
    SELECT ROWNUM RNUM, A.*
    FROM (SELECT * FROM some_table WHERE some_condition) A
)
SELECT FLOOR(((SELECT COUNT(*) FROM base) / 15) + 1) TOTAL_PAGES_TO_FETCH, 
       ((ROWNUM - MOD(ROWNUM, 15)) / 15) + 1 PAGE_TO_FETCH,
       B.*
FROM base B

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

Из набора результатов обрабатывайте 15 строк за раз. Последний набор строк может быть короче 15.

Ответ 4

Просто предложение:

Вы можете рассмотреть подход Google "1-10 из примерно 13 000 000 результатов" - запустить COUNT (*) в качестве быстрого образца по исходному запросу. Я предположил, что для данного ABC не более одного XYZ:

SELECT *
FROM (SELECT r.*, ROWNUM RNUM, 
      (SELECT COUNT(*) * 100
       FROM ABC SAMPLE(1) t0
       WHERE (t0.XYZ_ID = 751)
      ) RESULT_COUNT 
  FROM (SELECT t0.ABC_SEQ_ID AS c0, t0.FIRST_NAME, t1.SCORE
        FROM ABC t0, XYZ t1
        WHERE (t0.XYZ_ID = 751) 
        AND t0.XYZ_ID = t1.XYZ_ID 
        ORDER BY t0.RANK ASC) R)
WHERE RNUM between 1 and 15

Очевидно, что образец будет довольно неточным и переменным, поэтому он зависит от требований, подходит ли это или нет.

Ответ 5

Другим решением было бы создание материализованного представления, которое поддерживает подсчеты для каждого значения ABC.XYZ_ID - таким образом вы подталкиваете нагрузку на получение счета для процессов, которые вставляют/обновляют/удаляют строки в таблице.

Ответ 6

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

Oracle собственный инструмент Application Express (Apex) предлагает выбор вариантов разбивки на страницы:

  • Самый эффективный просто указывает, есть ли "больше" строк. Для этого он выбирает только одну строку, чем текущая максимальная страница (например, 31 строка для страницы, показывающая строки 16-30).
  • Или вы можете показать ограниченный счет, который может отображать "16-30 из 67" или "16-30 из более чем 200". Это означает выборки до 201 (в этом примере) строк. Это не так эффективно, как вариант 1, но более эффективен, чем вариант 3.
  • Или вы можете показать "16-30 из 13 945". Для этого Apex должен получить все 13,945, но отбросить все, кроме строк 15-30. Это самый медленный, наименее эффективный метод.

Псевдо-PL/SQL для параметра 3 (ваше предпочтение):

l_total := 15;
for r in 
  ( select * 
      from 
    ( select rownum rnum, a.*
        from (my_query) a
    )
    where rnum > 15
  )
loop
   l_total := l_total+1;
   if runum <= 30 then
      print_it;
   end if;
end loop;
show_page_info (15, 30, l_total);

Ответ 7

На основе ответа EvilTeach:

WITH
base AS
(
    SELECT (ROWNUM - 1) RNUM, A.*
    FROM (SELECT * FROM some_table WHERE some_condition) A
)
SELECT V.* FROM (
  SELECT FLOOR(((SELECT COUNT(*) FROM base) / 15) + 1) TOTAL_PAGES_TO_FETCH, 
         ((RNUM - MOD(RNUM, 15)) / 15) + 1 PAGE_TO_FETCH,
         B.*
  FROM base B
) V
WHERE V.PAGE_TO_FETCH = xx

где XX - это страница, которую вы хотите.

Вышеупомянутое решение включает небольшое исправление к исходному коду, из-за которого первая страница возвращает результаты PAGE_SIZE-1.