Oracle中分页查询的速度

Question

对我来说，这是一个永无止境的话题，我想知道我是否会忽略某些事情。本质上，我在应用程序中使用两种类型的SQL语句：

1. 具有“后备”限制的常规查询
2. 排序查询和分页查询

现在，我们讨论的是针对具有几百万条记录的表的一些查询，再加上另外五个具有几百万条记录的表的查询。显然，我们几乎不希望全部获取它们，这就是为什么我们有上述两种方法来限制用户查询的原因。

情况1 确实很简单。我们只是添加了一个额外的ROWNUM过滤器：

WHERE ...
  AND ROWNUM < ?

这相当快，因为​​Oracle的CBO会在执行计划时考虑使用此过滤器，并且可能会应用一项FIRST_ROWS操作（类似于/*+FIRST_ROWS*/提示所执行的操作）。

*但是， *情况2 对于Oracle来说比较棘手，因为没有LIMIT ... OFFSET其他RDBMS中的子句。因此，我们将“业务”查询嵌套在技术包装中，如下所示：

SELECT outer.* FROM (
  SELECT * FROM (
    SELECT inner.*, ROWNUM as RNUM, MAX(ROWNUM) OVER(PARTITION BY 1) as TOTAL_ROWS
    FROM (
      [... USER SORTED business query ...]
    ) inner
  ) 
  WHERE ROWNUM < ?
) outer
WHERE outer.RNUM > ?

请注意，TOTAL_ROWS计算该字段是为了知道即使不获取所有数据，我们将拥有多少页。现在，此分页查询通常非常令人满意。但是有时（如我所说，当查询5M
+记录时（可能包括未索引的搜索）），此过程将持续2-3分钟。

编辑 ：请注意，潜在的瓶颈不是那么容易解决，因为在分页之前必须应用排序！

我想知道，是国家的最先进的是模拟的LIMIT ... OFFSET，包括TOTAL_ROWS在甲骨文，还是有更好的解决方案，这将是由设计速度更快，例如，通过使用ROW_NUMBER()窗函数，而不是ROWNUM伪列？

Answer 1

与第2种情况的主要问题是，在许多情况下，整个查询结果集，以获取，然后进行排序 之前， 可以返回的第一个N行-除非ORDER
BY列索引和Oracle可以使用索引来避免排序。对于复杂的查询和大量数据，这可能需要一些时间。但是，您可以采取一些措施来提高速度：

1. 尝试确保内部SQL中没有调用任何函数-仅返回前20行，它们可能被调用500万次。如果您可以将这些函数调用移至外部查询，则将减少它们的调用。
2. 使用FIRST_ROWS_n提示来推动Oracle优化，以使您永远不会返回所有数据。

编辑：

另一个想法：您当前正在向用户显示 可以
返回数千或数百万行的报告，但是用户从不现实地翻阅所有行。您是否可以不强迫他们选择较小量的数据，例如将选择的日期范围限制为3个月（或其他日期）？