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

Question

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

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

现在我必须运行一个单独的 SQL 语句来对“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

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

Answer 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 个单独查询的性能，也许您应该考虑一种解决方案，例如 FrusteratedWithFormsDesigner 在他/她关于缓存记录计数的评论中提出的解决方案。

如果您定期使用数据库，我建议您获取一份 SQL Cookbook。这是一本出色的书，其中包含许多有用的技巧。

Answer 2

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

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

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

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

Answer 3

只是一个建议：

您可以考虑 Google“大约 13,000,000 个结果中的 1-10”方法 - 在原始查询上运行 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 

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

Answer 4

这有效吗？

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;

Answer 5

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

Answer 6

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 行。

Answer 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 结果。

Answer 8

是否有任何方法可以获取结果总数，而无需通过第二个查询执行此操作，即从上面的查询中获取结果？

对于 Oracle 12c，这可行：

-- use count analytic function
select a.*, count(*) over() as total_nb_results from mytable a
offset 10 rows fetch next 10 rows only 

它将返回每批 10 行，从第 10 条记录开始，以及请求返回的记录总数（如果没有“offset/fetch”子句）。

另请注意，您也可以完美地使用 Oracle 10g 中的分析 count 函数。