Oracle数据库中的ROWNUM和ORDER BY执行顺序

使用SQL查询Oracle表数据的时候，可能会有如下两种结果需求。

对查询结果集排序，并获得其排序前的行号

对结果集排序后，为每一行加入行号

对于上述两种结果需求，编写SQL语句的时候，需要注意ROWNUM赋值和ORDER BY的执行顺序。如果ORDERBY的基准字段是表的PrimaryKey，则查询执行过程是先对表进行排序，然后为排序后的表视图从第一行到最后一行赋予ROWNUM值。反之，如果ORDERBY的基准字段不是PK，则先从第一行到最后一行为表赋予ROWNUM值，然后进行排序。例如假设存在表TABLE_TEST，其数据如下。

TABLE_TEST

COLUMN_1            COLUMN_2            COLUMN_3            COLUMN_4            COLUMN_5

2010                            John                    010336622            Beijing                        19910609

 2011                          Jim                      010336633            Tokyo                        19911011

2012                            Kate                    010336611            Newark                        19920821

2013                            Richard              010336644            Paris                            19920115

2014                            Joseph                010336666            London                        19910726

有如下SQL语句查询数据。

SELECT ROWNUM, COLUMN_1, COLUMN_2

FROM TABLE_TEST

WHERE ROWNUM < 3

ORDER BY COLUMN_1 DESC;

那么，如果

COLUMN_1为表的主键，则查询结果为

ROWNUM      COLUMN_1            COLUMN_2

1                          2014                        Joseph

2                          2013                        Richard

COLUMN_1不是表的主键，则查询结果为

ROWNUM      COLUMN_1            COLUMN_2

2                          2011                          Jim

1                          2010                          John

从上述例子中可以看出根据排序列是否为主键，对排序和ROWNUM的影响。

对于文章最开始的两种情况，如果我们需要排除因为排序字段主键与否对执行顺序的影响。可以分别采用如下的查询方式。

SELECT*

FROM (

      SELECT

            ROWNUM

            , COLUMN_1

            , COLUMN_2

                    FROMTABLE_NM

      )ORDER BY COLUMN_1

        b.  SELECTROWNUM, A.*

            FROM (

                    SELECT

  COLUMN_1

  , COLUMN_2

                    FROMTABLE_NM

                    ORDERBY COLUMN_1

          )  A

利用上述方式，虽然那效率上不如非嵌套查询，但能达到我们的查询需求。

测试版本：ORACLE 11g Release 11.2

 另外从ORACLE 9i开始加入的ROW_NUMBER() OVER函数，在排序关系上和ROWNUM一致，但它比ROWNUM多了一些功能，它可以帮助实现最后若干行的操作。例如

SELECT *

FROM (

SELECTA.*,

ROW_NUMBER()OVER(PARTITION BY TRUNC(COLUMN_1)

ORDERBY COLUMN_1 DESC) AS ROW_NUM

                  FROMTABLE_NM A

)WHERE ROW_NUM <=2

Oracle中先ORDER BY再ROWNUM    http://www.linuxidc.com/Linux/2012-02/54525.htm

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2014-11/10932.htm

参考

oracle分析函数技术详解（配上开窗函数over()）

Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解

Rownum与Order by

order+by+rownum  百度

====================

Oracle中先ORDER BY再ROWNUM

Java代码

1. select t.*,t.rowid,rownum from test1 t  

name            KM              CJ      ROWID                   ROWNUM

张三      语文      80 AAAHhOAALAAATSIAAA 1
张三      数学      86 AAAHhOAALAAATSIAAB 2
张三      英语      75 AAAHhOAALAAATSIAAC 3
李四      语文      79 AAAHhOAALAAATSIAAD 4
李四      数学      85 AAAHhOAALAAATSIAAE 5
李四      英语      78 AAAHhOAALAAATSIAAF 6

Java代码

1. select t.*,t.rowid,rownum from test1 t where rownum<3 order by CJ  

张三      语文      80 AAAHhOAALAAATSIAAA 1
张三      数学      86 AAAHhOAALAAATSIAAB 2

采用嵌套的方式可以成功

Java代码

1. select t.*,t.rowid,rownum from (select * from test1 t order by CJ) t where rownum <3  

张三      英语      75 AAAHhOAALAAATSIAAF 1
李四      英语      78 AAAHhOAALAAATSIAAF 2

但是嵌套方式影响效率，如果非嵌套方式呢？
引用http://hi.baidu.com/yunfanleo/blog/item/09e51a1f064c66cba6866951.html中的话
rownum是一个伪列，和数据的组织方式相关，它不能唯一标识记录。
如果你没有定义主键或能唯一标识记录的字段的话，唯一标识记录的是rowid，
rowid不会再改变，但是如果你的表改变了表空间的话，rowid也可能会改变。

但是如果当你 将CJ字段置为主键后执行

Java代码

1. select t.*,t.rowid,rownum from test1 t where rownum<3 order by CJ  

张三      英语      75 AAAHhOAALAAATSIAAC 1
李四      英语      78 AAAHhOAALAAATSIAAF 2

=============================================

Oracle中先ORDER BY再ROWNUM，即实现带有排序的分页功能

with

tmptable as

（

SELECT ROWNUM r, COLUMN_1, COLUMN_2

FROM TABLE_TEST

ORDER BY COLUMN_1 DESC

）

SELECT * from tmptable WHERE    r  < =20 and r>10;