sql – Oracle中分页查询的速度

发布时间：2020-12-12 08:25:48 所属栏目：MsSql教程来源：网络整理

导读：这对我来说是一个永无止境的话题,我想知道我是否可以忽略某些东西.本质上我在应用程序中使用两种类型的SQL语句：具有“回退”限制的常规查询排序和分页查询现在,我们正在谈论对具有数百万条记录的表的一些查询,加上另外5个表,其中有几百万条记录.显然,我们

这对我来说是一个永无止境的话题,我想知道我是否可以忽略某些东西.本质上我在应用程序中使用两种类型的SQL语句：

>具有“回退”限制的常规查询
>排序和分页查询

现在,我们正在谈论对具有数百万条记录的表的一些查询,加上另外5个表,其中有几百万条记录.显然,我们几乎不想获取所有这些,这就是为什么我们有上述两种方法来限制用户查询.

案例1真的很简单.我们只需添加一个额外的ROWNUM过滤器：

WHERE ...
  AND ROWNUM < ?

这很快,因为Oracle的CBO将考虑其执行计划考虑此过滤器,并且可能应用FIRST_ROWS操作(类似于/ * FIRST_ROWS * / hint强制执行的操作).

但是,情况2与Oracle有点棘手,因为没有其他RDBMS中的LIMIT … OFFSET子句.所以我们将我们的“业务”查询嵌套在技术包装器中：

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,包括Oracle中的TOTAL_ROWS,还是有更好的解决方案,将通过设计更快速,例如通过使用ROW_NUMBER()窗口函数而不是ROWNUM伪列？

解决方法

情况2的主要问题是,在许多情况下,必须获取整个查询结果集,然后在返回前N行之前进行排序,除非ORDER BY列被索引,并且Oracle可以使用索引来避免排序.对于复杂的查询和大量数据,这可能需要一些时间.但是,可能会有一些事情可以提高速度：

>尝试确保在内部SQL中没有调用函数 – 这些可能会被调用500万次,以返回前20行.如果您可以将这些函数调用移动到外部查询,则它们将被调用较少.
>使用FIRST_ROWS_n提示来推动Oracle进行优化,因为您永远不会返回所有数据.

编辑：

另一个想法：你目前正在向用户展示一个报告,可以返回数千或数百万行,但是用户从来都不会真正地浏览它们.你不能强迫他们选择更少量的数据,例如通过将选择的日期范围限制为3个月(或任何)？

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!