在目前很多系統中，介面資料分頁（Page）已經成為一項通用功能。基本上，每種框架、結構和對應的資料庫，都有一些成熟的現成解決方案。在Oracle中，rownum偽列是初學者非常容易用錯的功能。本篇就介紹一個實際的分頁和rownum結合使用時候的故障案例。

1、問題簡述

系統後臺資料庫採用Oracle 11gR2版本。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

資料表inputfile_log記錄資料檔案日誌資訊。在介面上提供資料查詢、操作和排序。介面提供所有欄位的查詢排序功能。資料表主鍵為inputfile_log_seq，一個欄位為ISO_CODE。

在測試階段，測試人員不時報出某些特定欄位在排序過程中有一些問題，排序之後，跳轉到下一頁資料取值不變。但是，在架構層面，沒有明顯的故障和Bug存在，前端故障也大都是偶發性的。

一個偶然的機會，測試和開發人員定位到了問題的所在：對其他欄位進行排序處理之後，分頁功能一般不會有故障。只有在ISO_CODE進行排序，之後進行跳轉分頁的時候，才會有問題，而且問題也是偶發性，不具有必然性。

經過定位，發現頁面前端輸出的SQL語句有一些問題。頁面前端採用Hibernate實體對映類。分頁採用資料庫“真分頁”技術——資料庫只把要顯示的資料傳遞到前端。Hibernate負責SQL語句的生成執行。

定位的SQL語句為：

select *

from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

from (select * from inputfile_log t order by t.ISO_CODE) a1

where rownum <= 50)

where rwn > 45;

Oracle中分頁的語句方案很多，這種利用rownum取段的方法也是普遍接受的方法。問題在哪兒呢？下面我們執行語句看結果。

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE) a1

4 where rownum <= 50)

5 where rwn > 45;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

1901 AU 46

1903 AU 47

1906 AU 48

1142 AU 49

1157 AU 50

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE) a1

4 where rownum <= 55)

5 where rwn > 50;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

1901 AU 51

1903 AU 52

1906 AU 53

1142 AU 54

1157 AU 55

看出問題了，在SQL語句中，我們希望看到連續10條記錄。但是雖然替換了資料段範圍，但是結果集卻是相同的。

反映到頁面上，就是按照ISO_CODE進行排序之後，分頁功能失效，點選跳到下一頁，但是顯示的資料卻沒有變化。

2、分析和實驗

看似很詭異的問題，難道是Hibernate語句方案有問題？替換為其他的排序欄位，問題似乎不存在。

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

3 from (select * from inputfile_log t order by t.inputfile_log_seq) a1

4 where rownum <= 55)

5 where rwn > 50;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

997 AU 51

998 AU 52

999 AU 53

1000 AU 54

1001 AU 55

SQL>

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

3 from (select * from inputfile_log t order by t.inputfile_log_seq) a1

4 where rownum <= 50)

5 where rwn > 45;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

992 AU 46

993 AU 47

994 AU 48

995 AU 49

996 AU 50

排序結果正常。看來問題還是出在ISO_CODE上。

條條大路通羅馬，換一種方法，試試結果如何呢？Oracle中還有很多其他的分頁方案。

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE) a1)

4 where rwn > 45

5 and rwn <= 50;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

1316 AU 46

1317 AU 47

1318 AU 48

1319 AU 49

1323 AU 50

SQL>

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE) a1)

4 where rwn > 50

5 and rwn <= 55;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

1324 AU 51

1865 AU 52

1624 AU 53

1163 AU 54

1173 AU 55

不採用問題SQL的兩次擷取方法，而是將所有的rownum都取出來實體化，再利用範圍進行擷取。這樣做的結果也是正確的。

問題出在哪裡呢？SQL本質上是一種描述性語句，只要我們正確描述，絕大多數情況是可以將正確的結果返回的。但是這個案例下，描述本身沒有什麼問題，而且錯誤出現是偶發性的，令人疑惑。

3、執行計劃找線索

在所有的DBMS中，SQL語句並不能執行，都需要轉化為實際執行的“程式”。這種自動程式設計的程式視覺化結果，就是執行計劃。我們猜想執行計劃過程中可能有一些問題。

首先，我們檢查一下能返回正確結果的SQL結構。以下采用autotrace進行分析。

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE) a1)

4 where rwn > 45

5 and rwn <= 50;

已用時間: 00: 00: 00.01

執行計劃

----------------------------------------------------------

Plan hash value: 3700799345

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5598 | 158K| | 365 (1)| 00:00:05 |

|* 1 | VIEW | | 5598 | 158K| | 365 (1)| 00:00:05 |

| 2 | COUNT | | | | | | |

| 3 | VIEW | | 5598 | 89568 | | 365 (1)| 00:00:05 |

| 4 | SORT ORDER BY | | 5598 | 1322K| 1800K| 365 (1)| 00:00:05 |

| 5 | TABLE ACCESS FULL| INPUTFILE_LOG | 5598 | 1322K| | 70 (2)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("RWN"<=50 AND "RWN">45)

上面的執行計劃，表示Oracle首先（Step 4）將所有資料記錄根據ISO_CODE進行全排序，最後（Step 1）根據rwn設定的範圍進行資料篩選。這樣的結構意味著Oracle的確是需要將所有資料進行全排序動作，最後返回結果。

那麼，Hibernate生成的SQL語句，執行計劃如何呢？

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE) a1

4 where rownum <= 55)

5 where rwn > 50;

已用時間: 00: 00: 00.06

執行計劃

----------------------------------------------------------

Plan hash value: 4198067120

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 55 | 1595 | | 365 (1)| 00:00:05 |

|* 1 | VIEW | | 55 | 1595 | | 365 (1)| 00:00:05 |

|* 2 | COUNT STOPKEY | | | | | | |

| 3 | VIEW | | 5598 | 89568 | | 365 (1)| 00:00:05 |

|* 4 | SORT ORDER BY STOPKEY| | 5598 | 1322K| 1800K| 365 (1)| 00:00:05 |

| 5 | TABLE ACCESS FULL | INPUTFILE_LOG | 5598 | 1322K| | 70 (2)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("RWN">50)

2 - filter(ROWNUM<=55)

4 - filter(ROWNUM<=55)

注意，我們在此處看到了一個操作名稱為“SORT ORDER BY STOPKEY”，我們知道STOPKEY是標準rownum偽列行為。STOPKEY的效果是“點到即指”，也就是數出符合條數的結果集合之後，立即結束操作。

第二個限制條件，體現在了結果集合的“COUNT STOPKEY”上。那麼，我們遇到的問題是不是在於這個“SORT ORDER BY STOPKEY”呢？

冷靜下來想，rownum與STOPKEY都有一個共同的特點，就是不會將結果集合全部檢視。那麼，我們存在一個猜想：Oracle是不是有信心可以確定出，已經找到了合適的結果呢？

問題出在ISO_CODE上，由於業務特點要求，ISO_CODE取值離散程度很低。

SQL> select ISO_CODE, count(*) from inputfile_log group by ISO_CODE;

ISO_CODE COUNT(*)

-------- ----------

293

SE 198

（篇幅原因，有省略……）

AU 2396

16 rows selected

如果按照ISO_CODE進行排列，會有連續很多頁數都是取值AU。如果按照ISO_CODE單條件進行排列，那麼順序的差異體現在不同的ISO_CODE之間，而不是相同ISO_CODE取值之間。

簡單的說，有10條ISO_CODE=‘AU’的記錄。你和Oracle說列出前5條ISO_CODE=‘AU’的記錄和列出後5條ISO_CODE=‘AU’的記錄，Oracle是不承認這十條記錄之間存在順序差異關係。也就是說：我給你一個相對位置數量，因為沒有差異。

當排序欄位離散程度大，順序差異大的時候，這種情況就不會出現。並且，如果每頁的頁數超過相同值範圍，也不會出現問題。這就是為什麼測試人員反映問題是偶發性出現的原因了。

退一步說，“SORT ORDER BY STOPKEY”操作是Oracle對於排序過程的一種最佳化。雖然筆者不能看到真實的原始碼，也沒有很多相關的資料。可以猜想到這種方式的排序數目應該是小於單純排列的。

4、解決方案

那麼，怎麼辦呢？

筆者感覺，問題應該從多個方面思考，從多個方面解決。首先是需求，使用者真的存在按照這個離散程度如此之低的欄位進行排序嗎？而且，從業務上看，是不是每次集中在一個ISO_CODE上進行處理，也就是使用篩選功能的機率更高呢？所以，首先應該爭取取消對這個欄位的排序要求。最強大的最佳化領域就是業務方面的最佳化，最好的資料庫調優就是不讓資料庫做這個事情。這裡還要考慮對現有架構解決方案的衝擊。

如果不能從業務上免於使用這個，建議在架構層面為每次排序的時候，加上資料表主鍵列，提高差異離散度。

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE, t.inputfile_log_seq) a1

4 where rownum <= 50)

5 where rwn > 45;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

992 AU 46

993 AU 47

994 AU 48

995 AU 49

996 AU 50

SQL>

SQL> select *

2 from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

3 from (select * from inputfile_log t order by t.ISO_CODE, t.inputfile_log_seq) a1

4 where rownum <= 55)

5 where rwn > 50;

INPUTFILE_LOG_SEQ ISO_CODE RWN

----------------- -------- ----------

997 AU 51

998 AU 52

999 AU 53

1000 AU 54

1001 AU 55

問題解決。這個方法的缺點在於需要修改架構程式碼，加入一些判斷邏輯和處理邏輯。但是畢竟這個Bug是有解的。

5、結論

在Oracle中，rownum是一種使用方便的工具函式列、偽列，很多業務都可以透過這個方法快速解決。但是，一定要注意這個列並不是真實存在的資料列，它的使用是有很強的特點的。

在使用rownum的過程中，一定要謹慎謹慎再謹慎，多測試、多實驗。

Rownum分頁故障解決一例

相關文章