Oracle11g带分页的select查询

Question

在尝试从oracle11g数据库获取分页对象列表时，我面临一个很大的性能问题。

据我所知，以及我在网上查过的情况，在oracle11g中实现分页的唯一方法如下：示例：[页面=1，大小=100]

SELECT * FROM
  (
    SELECT pagination.*, rownum r__  FROM
      (
         select * from "TABLE_NAME" t
         inner join X on X.id = t.id
         inner join .....
         where ......
         order
      ) pagination
    WHERE rownum <= 200 
  )
WHERE r__ > 100

此查询中的问题是，从表“table\u NAME”中获取数据的最内部查询返回了大量数据，导致整个查询需要8秒钟（应用where子句后返回了大约200万条记录，其中包含9或10个join子句）。

这是因为最内部的查询是获取与where子句相关的所有数据，然后第二个查询是获取200行，第三个查询是排除前100行，以获取所需的第二页数据。

是否有一种方法可以在一个查询中实现这一点，即获取第二个页面所需的数据，而不必执行所有这些步骤并导致性能问题？

非常感谢。

Answer 1

这取决于您的排序选项（order by…）：数据库需要对整个数据集进行排序，然后再应用outerwhere rownum

如果删除您的order by子句，它将只提取200行。在某些情况下，oracle可以避免排序操作（例如，如果oracle可以使用某些索引以所需的顺序获取请求的数据）。顺便说一句，Oracle在出现rownum时使用优化的排序操作

您可以使用排序跟踪事件更深入地研究排序操作：alter session set events“10032 trace name context forever，level 10”

此外，有时最好使用分析函数，例如

select *
from (
   select
      t1.*
     ,t2.*
     ,row_number()over([partition by ...] order by ...) rn
   from t1
       ,t2
   where ...
   )
where rn <=200
and rn>=100

因为在某些特定情况下，Oracle可以将您的查询转换为将排序和排序筛选器谓词推到最早的步骤。