[ORACLE] ROWNUM概念/使用

ROWNUM可用於表示查詢結果集之資料列(Row)擷取次序，傳回第1筆資料列其ROWNUM為1，第二筆為2，依此類推。以CTAS建立測試資料並建立索引以進行相關測試，如下:

資料表/資料

CREATE TABLE ROWNUM_TEST

AS

  SELECT 5 Emp_No, '阿龍' Emp_Name

  FROM DUAL  

  UNION ALL

  SELECT 4 Emp_No, '阿丁' Emp_Name

  FROM DUAL   

  UNION ALL

  SELECT 3 Emp_No, '珍珍' Emp_Name

  FROM DUAL

  UNION ALL

  SELECT 2 Emp_No, '大明' Emp_Name

  FROM DUAL

  UNION ALL

  SELECT 1 Emp_No, '鐵雄' Emp_Name

  FROM DUAL;

建立INDEX

CREATE INDEX IX_ROWNUM_T ON ROWNUM_T (Emp_Name);

若於SELECT敍述句中使用ORDER BY子句時，則ROWNUM順序可能有所差異，ROWNUM是在排序前就先賦予之編號，因此排序後第1名資料之ROWNUM未必為1，如需取得排序後特定資料（如第1筆），則需於子查詢(Subquery)中先進行排序後再進行限縮，如下所示。。

#	SQL	結果	說明
1	SELECT /*+FULL(A)*/ ROWNUM, EMP_NAME FROM ROWNUM_TEST A  WHERE 1=1 AND EMP_NAME LIKE '阿%'
2	SELECT /*+FULL(A)*/ ROWNUM, EMP_NAME FROM ROWNUM_TEST A  WHERE 1=1    AND EMP_NAME LIKE '阿%' ORDER BY EMP_NAME		排名第1位之ROWNUM為2，由於ROWNUM較ORDER BY優先執行，因此僅改變該筆資料之順序，ROWNUM則否。
3	SELECT ROWNUM, EMP_NAME FROM  (  SELECT  /*+FULL(A)*/ EMP_NAME  FROM ROWNUM_T A   WHERE 1=1    AND EMP_NAME LIKE '阿%'  ORDER BY EMP_NAME  ) WHERE ROWNUM = 1		子查詢中進行排序，再由此結果以ROWNUM取得編號。

另外，如欲取得結果集中第2筆資料

#	SQL	結果	說明
1	SELECT /*+FULL(A)*/ ROWNUM, EMP_NAME FROM ROWNUM_T A  WHERE 1=1   AND EMP_NAME LIKE '阿%'        AND ROWNUM = 2	無資料	ROWNUM=2:無傳回任何值。
2	SELECT /*+FULL(A)*/ ROWNUM, EMP_NAME FROM ROWNUM_T A  WHERE 1=1   AND EMP_NAME LIKE '阿%'        AND ROWNUM <= 2		ROWNUM <=2：傳回2筆資料

當使用ROWNUM=2時，則無任何資料被傳回，可參考以下ROWNUM運算步驟，即可得知。由［步驟1、2］，第1筆資料擷取傳回並賦予編號為1，但無法滿足［步驟3］之條件符合檢查，因此無任何資料被傳回。ROWNUM=1為特列，若需傳回其他特定資料列，則建議使用ROW_NUMBER函數較為方便。

ROWNUM運算步驟（資料來源：O'Reilly,『SQL Cookbook』, P.9）

1.  Oracle executes your query. 2.  Oracle fetches the first row and calls it row number 1. 3.  Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row. 4.  Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth). 5.  Go to step 3.

另外，ROWNUM序號可能因查詢時INDEX使用與否或不同而可能不同，如下所示。

#	FULL TABLE SCAN	INDEX
SQL	SELECT /*+FULL(A)*/        ROWNUM, EMP_NAME FROM ROWNUM_TEST A  WHERE 1=1 AND EMP_NAME LIKE '阿%'	SELECT /*+INDEX(A, IX_ROWNUM_T) */         ROWNUM, EMP_NAME FROM ROWNUM_TEST A  WHERE 1=1       AND EMP_NAME LIKE '阿%'
結果
執行計劃

ROWNUM使用上有以下幾個特點：

l   與ORDER BY搭配使用時，需使用子查詢。

l   ROWNUM > N 或 ROWNUM = N (N>1)永遠不成立（滿足）。

l   ROWNUM序號可能因查詢時INDEX使用與否或不同而可能不同。

l   ROWNUM為保留字，如後續查詢需參照使用，請用別名(Alias)。

以上所提之範例，ROW_NUMBER函數或為較佳之選擇。刪除或異動大量條件資料時，則可使用ROWNUM以限制批次異動量，避免耗用過多之UNDO空間。