Oracle資料庫中的ROWNUM和ORDER BY執行順序

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

 2011                          Jim                      010336633            Tokyo                        19911011

2010                            John                    010336622            Beijing                        19910609

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