oracle 优化5
Oracle 的分页查询语句基本上可以按照本文给出的格式来进行套用
分页查询格式:
SELECT * FROM (
    SELECT A.*,  ROWNUM RN
    FROM (SELECT * FROM TABLE_NAME) A
    WHERE ROWNUM <= 40
)
WHERE RN >= 21
其中最内层的查询 SELECT * FROM TABLE_NAME 表示不进行翻页的原始查询语句. ROWNUM <= 40 和 RN >= 21 控制分页查询的每页的范围.
    上面给出的这个分页查询语句, 在大多数情况拥有较高的效率. 分页的目的就是控制输出结果集大小, 将结果尽快的返回. 在上面的分页查询语句中, 这种考虑主要体现在 WHERE ROWNUM <= 40 这句上.
    选择第 21 到 40 条记录存在两种方法, 一种是上例中展示的在查询的第二层通过 ROWNUM <= 40 来控制最大值, 在查询的最外层控制最小值. 而另一种方式是去掉查询第二层的 WHERE ROWNUM <= 40 语句, 在查询的最外层控制分页的最小值和最大值. 查询语句如下:
SELECT * FROM (
    SELECT A.*,  ROWNUM RN
    FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
对比这两种写法, 绝大多数的情况下, 第一个查询的效率比第二个高得多.
    这是由于 CBO 优化模式下, Oracle 可以将外层的查询条件推到内层查询中, 以提高内层查询的执行效率. 对于第一个查询语句, 第二层的查询条件 WHERE ROWNUM <= 40 就可以被 Oracle 推入到内层查询中, 这样 Oracle 查询的结果一旦超过了 ROWNUM 限制条件, 就终止查询将结果返回了.
    而第二个查询语句, 由于查询条件 BETWEEN 21 AND 40 是存在于查询的第三层, 而 Oracle 无法将第三层的查询条件推到最内层(即使推到最内层也没有意义, 因为最内层查询不知道 RN 代表什么). 因此, 对于第二个查询语句, Oracle 最内层返回给中间层的是所有满足条件的数据, 而中间层返回给最外层的也是所有数据. 数据的过滤在最外层完成, 显然这个效率要比第一个查询低得多.
    上面分析的查询不仅仅是针对单表的简单查询, 对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效.
    这里就不对包含排序的查询进行说明了, 下面会通过例子来详细说明. 下面简单讨论一下多表联合的情况. 对于最常见的等值表连接查询, CBO 一般可能会采用两种连接方式 NESTED LOOP 和 HASH JOIN(MERGE JOIN 效率比 HASH JOIN 效率低, 一般 CBO 不会考虑). 在这里, 由于使用了分页, 因此指定了一个返回的最大记录数, NESTED LOOP 在返回记录数超过最大值时可以马上停止并将结果返回给中间层, 而 HASH JOIN 必须处理完所有结果集(MERGE JOIN 也是). 那么在大部分的情况下, 对于分页查询选择 NESTED LOOP 作为查询的连接方法具有较高的效率(分页查询的时候绝大部分的情况是查询前几页的数据, 越靠后面的页数访问几率越小).
因此, 如果不介意在系统中使用 HINT 的话, 可以将分页的查询语句改写为:
SELECT /*+ FIRST_ROWS */ * FROM (
    SELECT A.*,  ROWNUM RN
    FROM (SELECT * FROM TABLE_NAME) A
    WHERE ROWNUM <= 40
)
WHERE RN >= 21