ROWNUM与ROW_NUMBER()的体验

主要是在网上看到了一些关于排序分页的帖子，个人感觉有些不妥，就写出SQL进行了测试下，下面列出结果与看法。

      通常先排序再分页都是使用ROWNUM伪列，通过将查询结果先进行排序，再使用两层SQL将查询结果进行分页，例子如：
Sql代码

   1. SELECT  * 
   2.   FROM  ( SELECT  ROWNUM row_, t.* 
   3.           FROM  ( SELECT    * 
   4.                     FROM  fltk 
   5.                 ORDER   BY  ID) t 
   6.          WHERE  ROWNUM < 10) 
   7.  WHERE  row_ >= 0 

SELECT *
  FROM (SELECT ROWNUM row_, t.*
          FROM (SELECT   *
                    FROM fltk
                ORDER BY ID) t
         WHERE ROWNUM < 10)
 WHERE row_ >= 0

      执行计划:

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  VIEW                          |         |
|   2 |   COUNT STOPKEY                |         |
|   3 |    VIEW                        |         |
|   4 |     TABLE ACCESS BY INDEX ROWID| FLTK    |
|   5 |      INDEX FULL SCAN           | PK_FLTK |
--------------------------------------------------


      还有一种排序分页的做法是使用分析函数ROW_NUMBER()，有人认为这种实现方法更加的高效。以下是SQL语句，实现的目标结果完全相同。
Sql代码

   1. SELECT  * 
   2.   FROM  ( SELECT  ROW_NUMBER () OVER ( ORDER   BY  ID) row_, fltk.* 
   3.           FROM  fltk) 
   4.  WHERE  row_ >= 0  AND  row_ < 10; 

SELECT *
  FROM (SELECT ROW_NUMBER () OVER (ORDER BY ID) row_, fltk.*
          FROM fltk)
 WHERE row_ >= 0 AND row_ < 10;

      执行计划：

-----------------------------------------
| Id  | Operation                | Name |
-----------------------------------------
|   0 | SELECT STATEMENT         |      |
|   1 |  VIEW                    |      |
|   2 |   WINDOW SORT PUSHED RANK|      |
|   3 |    TABLE ACCESS FULL     | FLTK |
-----------------------------------------


      查询的数据量是100万条，最后的结果是使用ROWNUM来查询为16ms左右,而使用ROW_NUMBER()来进行查询需要1s，通过执行计划分析，ROWNUM中使用了索引，而 ROW_NUMBER没有使用索引，这是导致两者性能差别最大的地方。

      然后使用没有索引的列进行排序，最后的结果是两者都需要1s，性能差异不大。

      由于性能差异不明显，体现不出两者的差别，于是将检索范围做了调整，如下图所示:

      由上可见，无论是在对数据量前面开始进行分页，还是选择分页后面的的结果，还是分页中的每页的内容都很多，这两者在查询效率上都没有很明显的差异，但是当对有索引列进行排序的时候，ROWNUM有着很明显的优势。