SQL (Oracle) 使用分页查询时如何获取结果总数？

(Oracle) How get total number of results when using a pagination query?

提问by BestPractices

I am using Oracle 10g and the following paradigm to get a page of 15 results as a time (so that when the user is looking at page 2 of a search result, they see records 16-30).

我使用 Oracle 10g 和以下范例一次获得 15 个结果的页面（这样当用户查看搜索结果的第 2 页时，他们会看到记录 16-30）。

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

Right now I'm having to run a separate SQL statement to do a "select count" on "my_query" in order to get the total number of results for my_query (so that I can show it to the user and use it to figure out total number of pages, etc).

现在我必须运行一个单独的 SQL 语句来对“my_query”进行“选择计数”，以获得 my_query 的结果总数（以便我可以向用户显示它并使用它来找出总页数等）。

Is there any way to get the total number of results without doing this via a second query, i.e. by getting it from above query? I've tried adding "max(rownum)", but it doesn't seem to work (I get an error [ORA-01747] that seems to indicate it doesnt like me having the keyword rownum in the group by).

有没有办法在不通过第二个查询执行此操作的情况下获得结果总数，即通过从上面的查询中获取它？我已经尝试添加“max(rownum)”，但它似乎不起作用（我收到一个错误 [ORA-01747]，这似乎表明它不喜欢我在 group by 中使用关键字 rownum）。

My rationale for wanting to get this from the original query rather than doing it in a separate SQL statement is that "my_query" is an expensive query so I'd rather not run it twice (once to get the count, and once to get the page of data) if I dont have to; but whatever solution I can come up with to get the number of results from within a single query (and at the same time get the page of data I need) should not add much if any additional overhead, if possible. Please advise.

我想从原始查询中获取它而不是在单独的 SQL 语句中执行它的基本原理是“my_query”是一个昂贵的查询，所以我宁愿不运行它两次（一次获取计数，一次获取计数）数据页）如果我不需要；但无论我能想出什么解决方案来从单个查询中获取结果数量（同时获取我需要的数据页面），如果可能的话，如果有任何额外的开销，都不应该增加太多。请指教。

Here is exactly what I'm trying to do for which I receive an ORA-01747 error because I believe it doesnt like me having ROWNUM in the group by. Note, If there is another solution that doesnt use max(ROWNUM), but something else, that is perfectly fine too. This solution was my first thought as to what might work.

这正是我正在尝试做的事情，我收到了一个 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 -------- Note, based on the first comment I tried the following that appears to work. I dont know how well it performs versus other solutions though (I'm looking for the solution that fufills my requirement but performs the best). For example, when I run this it takes 16 seconds. When I take out the COUNT(*) OVER () RESULT_COUNT it takes just 7 seconds:

--------- 编辑-------- 注意，根据第一条评论，我尝试了以下似乎有效的方法。我不知道它与其他解决方案相比的性能如何（我正在寻找满足我的要求但性能最好的解决方案）。例如，当我运行它时需要 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

The explain plan changes from doing a SORT (ORDER BY STOP KEY) to do a WINDOW (SORT).

解释计划从执行排序（按停止键排序）更改为执行窗口（排序）。

Before:

前：

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

After:

后：

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

回答by Elliot Vargas

I think you have to modify your query to something like this to get all the information you want on a "single" query.

我认为您必须将您的查询修改为这样的内容才能在“单个”查询中获得您想要的所有信息。

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 

The reason is that the COUNT(*) OVER()window function gets evaluated after the WHEREclause, hence not giving the total count of records but the count of records that satisfy the ROWNUM <= 30condition.

原因是COUNT(*) OVER()窗口函数在WHERE子句之后进行评估，因此不给出记录总数，而是给出满足ROWNUM <= 30条件的记录数。

If you cannot accept the performance ot this query, or of executing 2 separate queries, maybe you should think about a solution like the one proposed by FrustratedWithFormsDesignerin his/her comment about caching the count of records.

如果您不能接受此查询或执行 2 个单独查询的性能，也许您应该考虑一种解决方案，例如FrustratedWithFormsDesigner在他/她关于缓存记录计数的评论中提出的解决方案。

If you work with databases on a regular basis I recommend you get a copy of SQL Cookbook. It is an exceptional book with lots of useful tips.

如果您经常使用数据库，我建议您获取SQL Cookbook的副本。这是一本特别的书，里面有很多有用的技巧。

回答by Jeffrey Kemp

Just a suggestion:

只是一个建议：

You could consider the Google "1-10 of approximately 13,000,000 results" approach - run the COUNT(*) as a quick sample over the original query. I've assumed here that there is at most one XYZfor a given ABC:

您可以考虑使用 Google 的“大约 13,000,000 个结果中的 1-10 个”方法 - 运行 COUNT(*) 作为原始查询的快速示例。我在这里假设XYZ给定的最多有一个ABC：

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 

Obviously, the sample will be quite inaccurate and variable, so it depends on the requirements whether this is appropriate or not.

显然，样本会非常不准确和可变，因此这是否合适取决于要求。

回答by Jeffrey Kemp

Another solution would be to create a materialized view that maintains counts for each value of ABC.XYZ_ID- that way you push the burden of getting the count to processes that insert/update/delete rows in the table.

另一种解决方案是创建一个实体化视图，该视图维护每个值的计数ABC.XYZ_ID- 这样您就可以将获取计数的负担推给在表中插入/更新/删除行的进程。

回答by FrustratedWithFormsDesigner

Does this work?

这行得通吗？

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;

回答by Tony Andrews

No, you can't do it without either running the query twice, or running it once and fetching and caching allthe rows to count them before starting to display them. Neither is desirable, especially if your query is expensive or potentially returns a lot of rows.

不，如果不运行查询两次或运行一次并获取和缓存所有行以在开始显示它们之前对其进行计数，您就无法做到这一点。两者都不是可取的，特别是如果您的查询很昂贵或可能返回很多行。

Oracle's own Application Express (Apex) tool offers a choice of pagination options:

Oracle 自己的 Application Express (Apex) 工具提供了多种分页选项：

1. The most efficient just indicates whether or not there are "more" rows. To do this it fetches just one more row than the current page maximum (e.g. 31 rows for page showing rows 16-30).
2. Or you can show a limited count that may show "16-30 of 67" or "16-30 of more than 200". This means is fetches up to 201 (in this example) rows. This is not as efficient as option 1, but more efficient than option 3.
3. Or you can, indeed, show "16-30 of 13,945". To do this Apex has to fetch all 13,945 but discard all but rows 15-30. This is the slowest, least efficient method.

1. 最有效的只是指示是否有“更多”行。为此，它仅比当前页面的最大值多获取一行（例如，显示第 16-30 行的页面为 31 行）。
2. 或者您可以显示有限的计数，可能会显示“16-30 of 67”或“16-30 of more than 200”。这意味着最多提取 201（在本例中）行。这不如选项 1 有效，但比选项 3 更有效。
3. 或者，您确实可以显示“13,945 个中的 16-30 个”。为此，Apex 必须获取所有 13,945，但丢弃除第 15-30 行之外的所有行。这是最慢、效率最低的方法。

The pseudo-PL/SQL for option 3 (your preference) would be:

选项 3（您的偏好）的伪 PL/SQL 将是：

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);

回答by EvilTeach

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

This query will calculate how many groups of pages you will need to fetch, and fetch the data as one query.

此查询将计算您需要获取多少组页面，并将数据作为一个查询获取。

From the result set, process 15 rows at a time. The very last set of rows, may be shorter than 15.

从结果集中，一次处理 15 行。最后一组行可能少于 15。

回答by Spyros

To build on EvilTeach's answer:

以 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

where XX is the page you want.

其中 XX 是您想要的页面。

The above solution includes a small bugfix to the original code that caused the first page to return PAGE_SIZE - 1 results.

上述解决方案包括对原始代码的一个小错误修正，导致第一页返回 PAGE_SIZE - 1 结果。

回答by noob

For Oracle 12c, this would work:

对于 Oracle 12c，这将起作用：

select a.*, count(*)over() from mytable a offset 10 rows fetch first 10 rows only