ROWNUM与ROW_NUMBER()

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

 

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

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语句，实现的目标结果完全相同。

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有着很明显的优势。

评论

4 楼 Eric.Yan 2012-05-31  

学习了 ，
但是有一个问题哈，‘ROWNUM中使用了索引’，自带的索引还是人为加上去的索引？

3 楼 miaow 2009-12-17  

我之前说差50％的数据量还没有百万，大概二三十万行的样子。
这套分组函数，多数用索引会很奇怪，特别是加上partition by之后。可能为此就统一先取进来了再处理了。
如果是都先取进来，加个排序值伪列比要排序的order by性能好一点也不奇怪啊。

2 楼 蔡华江 2009-12-17  

可能是百万级的数据量实在太少了，还不足以突出两者的性能差异。
而row_number()不能使用索引实在是个问题，不知道是不是使用索引有什么限制条件？

1 楼 miaow 2009-12-17  

我印象里以前在实际项目中测过，row_number()有一定性能优势，大约高50％的样子。
你分析的用到索引的差异，可能有些关系。
你的场景是没有where条件又刚好有一个可以用的索引。
我用到的场景都是where条件一大砣又不是按索引列排序（按最后操作时间，没法加索引）。