2013年9月26日 星期四

[ORACLE] ROWNUM概念/使用

ROWNUM可用於表示查詢結果集之資料列(Row)擷取次序,傳回第1筆資料列其ROWNUM1,第二筆為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位之ROWNUM2,由於ROWNUMORDER 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運算步驟,即可得知。由[步驟12],第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空間。

沒有留言:

張貼留言