指定欄位排序時Oracle的Rownum和MSSql的Top取資料之差異

2012-01-17

3920
0
2012-04-20

Dotblogs 的標籤：Oracle ,MSSql ,DB

昨天看到一個排序後取最大一筆資料的 SQL，結果發現 Oracle 的行為與我預期不同。快速筆記一下，並和 MSSql 做一個差異記錄。原始 Table data：

SN NAME
10 111
20 222
15 333

請注意，此 Table 沒有設定任何主鍵、索引鍵。

目的是要取得 SN 最大的那筆資料，所以 Oracle 語法和取得結果如下：

語法：SELECT * FROM LEO_TEST WHERE "NAME" LIKE '%%' AND ROWNUM = 1 ORDER BY SN DESC;

結果：10 111

MSSql 語法和取得結果如下：

語法：SELECT TOP 1 * FROM LEO_TEST WHERE NAME LIKE '%%' ORDER BY SN DESC;

結果：20 222

很明顯，MSSql 取得的結果才是我們要的。那該如何讓 Oracle 也能取得 20 這筆資料呢？研究結果，除了把語法改成子查詢之外，最簡單的解法是把 SN 欄位設定為主鍵或索引鍵即可。

其實設定主鍵/索引這件事有點出乎我意料，因為我原本預期，以 Oracle 語法來看，因為 Rownum 是放在 Where 區段中執行，所以不管如何，Where 條件應該會先做完，再執行 Order by，但事實不然！以本次實測結果來看，若排序欄位非主鍵/索引，則會先執行 Where 條件，再排序，但是若排序欄位為主鍵/索引，則會先排序，再執行 Where 條件。

從執行計畫中（Explain plan）中，亦可驗證此論點：

尚未設定 SN 為主鍵/索引的執行計畫：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2990164199
--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  SORT ORDER BY      |          |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |          |       |       |            |          |
|   3 |    TABLE ACCESS FULL| LEO_TEST |     3 |   105 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)

已設定 SN 為主鍵之執行計畫：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1265933097
--------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    35 |     2   (0)| 0
|*  1 |  COUNT STOPKEY               |          |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| LEO_TEST |     3 |   105 |     2   (0)| 0
|   3 |    INDEX FULL SCAN DESCENDING| TEST     |     1 |       |     1   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)

--------
沒什麼特別的~
不過是一些筆記而已

回首頁