Oracle分页查询

看到过三种分页查询语句

1.一般我们会用这个语句：

SELECT * FROM(
    SELECT A.*, ROWNUM RN
    FROM (SELECT * FROM TABLE_NAME) A
    WHERE ROWNUM <= 20
)
WHERE RN >= 11

     由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于这个查询语句，第二层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

2.绝大多数的情况下这个效率要比第一个低

SELECT * FROM(
    SELECT A.*, ROWNUM RN
    FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 11 AND 20

     由于查询条件BETWEEN 11 AND 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层。

3.两层嵌套查询

SELECT * FROM(
    SELECT A.*,ROWNUM RN FROM TABLE_NAME A
)
WHERE RN BETWEEN 11 AND 20

       经过验证（特殊条件除外）：第一种的执行时间最短，后面两个差不多。
 
     如果数据量很大的话可能会出现问题：
 
先说下oracle读取记录的顺序：
     Oracle会根据具体的数据块的存储返回记录。oracle数据库是没有默认排序的，要排序必须加上order by 。因为 oracle是按块进行读取数据的。如果数据按顺序存储，则可能使读取出来的数据是按顺序的，给用户误解为默认排序，oracle没有进行任何排序操作，rowid表示的是数据存放的数据块内部地址，如果没有要求排序，oracle会顺序的从数据块中读取符合条件的数据返回到客户端，不过看起来好像是按照rowid排序似的。如果数据量足够大，即使相同的语句，都有可能不同的结果。一般而言：表是堆表，所以是无序的他是按照也即物理存放顺序来读取的。rowid 是自动随行生成的。
      此时的排序就需要加order by（或者你需要按照某个字段分页也会用到order by）。
      前两种可以在第一层里面加上order by完全没问题。
      第三种也就是两层嵌套查询即使第一层加上order by。因为rownum在每查出一条记录就从1开始递增赋值，最后才order by排序。这样order by就完全没有意思。因为你最后还是按照rownum过滤的，所以第三种在这就行不通了。
     综上所述：还是用第一种效率会高，如果需要order by可以在第一层加上。
      一个简单的表的实验：11万条数据他们的查询时间：0.015，0.031，0.031
      虽然他们相对时间是有差距的，但是时间都是很短的。影响程序效率的一般都会是连接数据库的次数。我们主要应该关注的是少连几次数据库。