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

我正在使用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"执行"select count"以获取my_query的结果总数(这样我就可以向用户显示它并使用它来弄清楚总页数等).

有没有办法获得结果的总数而不通过第二个查询,即从上面的查询中获取它？我已经尝试添加"max(rownum)",但它似乎不起作用(我得到一个错误[ORA-01747]似乎表明它不喜欢我在组中有关键字rownum).

我希望从原始查询中获取此信息而不是在单独的SQL语句中执行此操作的理由是"my_query"是一个昂贵的查询,所以我宁愿不运行它两次(一次得到计数,一次得到数据页面)如果我不需要; 但是,如果可能的话,我可以在单个查询中获得结果数量(同时获取我需要的数据页面)的任何解决方案都不应该增加很多额外的开销.请指教.

这正是我正在尝试做的事情,因为我认为它不喜欢我在组中使用ROWNUM,因此我收到了ORA-01747错误.注意,如果有另一种解决方案不使用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

Elliot Varga.. 23

我认为您必须将查询修改为类似的内容,以便在"单个"查询中获取所需的所有信息.

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的副本.这是一本特别的书,有很多有用的提示.

我认为您必须将查询修改为类似的内容,以便在"单个"查询中获取所需的所有信息.

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的副本.这是一本特别的书,有很多有用的提示.