Oracle 比较分页方法-偏移与搜索

你好，问一下汤姆团队。

我想在连接到Oracle 18c数据库的报告中进行分页。

我已经阅读了一些方法来进行分页，例如: offset和seek。我有几个问题

1.什么方法是最好的？我听说偏移方法会导致错误的结果。

2.你可以写一个两个方法通过结果分页的例子吗？

提前感谢。

问候，

当您使用OFFSET时，数据库会读取所有offset行加上您希望看到的N。

使用seek方法，您可以记住从上一页读取的最后一行的主键/唯一键值。

例如，假设您正在按10个批次分页。到第6页时，offset读取60行-前五页的50行，当前页的10行:

create table t ( c1 primary key, c2, c3 ) as 
  select level c1,
         mod ( level, 2 ) c2,
         rpad ( 'stuff', 500, 'f' ) c3
  from   dual
  connect by level <= 100;
  
set serveroutput off
alter session set statistics_level = all;

select * from t
order  by c1 
offset 50 rows
fetch  next 10 rows only;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

---------------------------------------------------------------------------------    
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |    
---------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT              |              |      1 |        |     10 |    
|*  1 |  VIEW                         |              |      1 |     60 |     10 |    
|*  2 |   WINDOW NOSORT STOPKEY       |              |      1 |     60 |     60 |    
|   3 |    TABLE ACCESS BY INDEX ROWID| T            |      1 |    100 |     60 |    
|   4 |     INDEX FULL SCAN           | SYS_C0022223 |      1 |     60 |     60 |    
---------------------------------------------------------------------------------   

注意计划中第2-4行的A行列中的60行。

而使用seek方法，您会记住第5页上的最后一个值是50。所以你搜索c1> 50的行，并得到下一个10:

select * from t
where  c1 > 50
order  by c1 
fetch  first 10 rows only;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
  
---------------------------------------------------------------------------------    
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |    
---------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT              |              |      1 |        |     10 |    
|*  1 |  VIEW                         |              |      1 |     10 |     10 |    
|*  2 |   WINDOW NOSORT STOPKEY       |              |      1 |     10 |     10 |    
|   3 |    TABLE ACCESS BY INDEX ROWID| T            |      1 |     10 |     10 |    
|*  4 |     INDEX RANGE SCAN          | SYS_C0022223 |      1 |        |     10 |    
---------------------------------------------------------------------------------  

总共读取了10行。因此，当您进一步浏览结果时，OFFSET FETCH NEXT将逐渐变慢。但是seek方法需要相似的时间。

只有当人们逐个浏览结果时，您才能使用seek方法。如果我直接从第一页跳到第六页，则您不知道第五页上的最后一个值。因为你没去过!

在这种情况下，您可以将这两种技术结合起来。从第一页的最后一个值开始。但是偏移到第6页:

select * from t
where c1 > 10
order  by c1 
offset 40 rows
fetch  next 10 rows only;

值得注意的是，这两种方法都可能导致 “错误” 的结果。

接下来，如果另一个会话插入在较早的页面上排序的行，您将看到重复的行:

select c1 from t
order  by c1 
fetch  first 5 rows only;

C1   
    1 
    2 
    3 
    4 
    5 

insert into t values ( 0, 0, 'new' );

select c1 from t
order  by c1 
offset 5 rows
fetch  next 5 rows only;

C1   
    5 <- 5 again!
    6 
    7 
    8 
    9 
   
select c1 from t
where  c1 > 5
order  by c1 
fetch  first 5 rows only;

C1   
    6 
    7 
    8 
    9 
   10 

如果在获取之间，另一个会话更新了第1页上没有出现的行，那么现在它将出现，那么您将永远不会看到这两种方法的该行:

rollback;

select c1 from t
where  c2 = 0
order  by c1 
fetch  first 5 rows only;

C1   
    2 
    4 
    6 
    8 
   10 

update t
set    c2 = 0
where  c1 = 9;

select c1 from t
where  c2 = 0
order  by c1 
offset 5 rows
fetch  next 5 rows only;

C1   
   10 <-- never see 9; 10 again!
   12 
   14 
   16 
   18 
   
select c1 from t
where  c1 > 10
and    c2 = 0
order  by c1 
fetch  first 5 rows only;

C1   
   12 <-- never see 9
   14 
   16 
   18 
   20 

无论您使用哪种方法，如果人们经常在结果中回页并转发定期更改的数据，客户将看到意想不到的结果。但是seek方法通常 “错误较少”，而且速度更快。

你如何管理这取决于你的要求。解决方案包括:

-预取大量页面 (例如20页)，并在应用程序层缓存这些页面。当用户点击next时，只需从缓存中获取下一个N。这是基于人们很少经过第2-3页的原则。这给出了稳定的结果，但是对第一页的查询会稍微慢一点

-使用闪回查询获得与第一页加载时间一致的结果。