Rownum和OrderBy的執(zhí)行順序造成的影響

6月的某天下午，某用戶反饋，醫(yī)生站首頁中病案附加項目的順序亂了，影響醫(yī)生的正常工作。

專注于為中小企業(yè)提供網(wǎng)站制作、成都網(wǎng)站設計服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)建昌免費做網(wǎng)站提供優(yōu)質(zhì)的服務。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設實現(xiàn)規(guī)模擴充和轉變。

從跟蹤出來的SQL看到，執(zhí)行得到的數(shù)據(jù)沒有按預期的方式排序，但是，相同的SQL在測試庫運行卻能得到正常排序的結果。

院方近期沒有做什么調(diào)整，懷疑跟一個月前我們轉移歷史數(shù)據(jù)之前的一系列性能優(yōu)化調(diào)整有關。

經(jīng)過一番分析，最終，通過重新收集該SQL涉及到的表的統(tǒng)計信息后，問題得到解決。

感覺有點兒不可思議吧？

統(tǒng)計信息收集不正確，還會影響產(chǎn)品功能的正常性？

有些事情的真相并不是我們看到那樣，就像雞血在磨心上點了幾下之后，小孩的肚子就不痛了，如果你相信這樣的巫術，可能會影響到你對很多事情的判斷，有時，我們看到的并不一定是真相，倒不是因為對未知的知識缺乏敬畏，而是基本的邏輯推理問題，以及探尋真相的鉆研精神。

統(tǒng)計信息收集確實是解決很多性能問題的一副靈藥，但對這個問題來說，收集統(tǒng)計信息只是其中的一種臨時解決辦法，根本的原因還是SQL書寫方面存在問題。

下面我們來一步一步解開事情的真相。

SQL語句如下：

select rownum as序號,編碼,名稱,內(nèi)容 from病案項目 order by編碼

多么簡單的SQL，不像那些需要翻幾頁才能看得完整的變態(tài)SQL，這種簡單的SQL除了理解起來更節(jié)約時間之外，還減少了很多可能導致驗證結果偏差的干擾。

按開發(fā)人員的預期，先排序再對結果集進行編號，Rownum作為Oracle下特有的“偽列”，作用就是根據(jù)數(shù)據(jù)結果行數(shù)產(chǎn)生一個從1開始遞增的行號。

有一定基礎的同學可能一下就看出的不對之處：

Order by和Rownum在同一層次時，排序是最后執(zhí)行的，先取行號自然就無法得到想要的按排序結果遞增的行號，所以，如下所示，序號列就是“亂序”的。

那么，問題來了，既然這個SQL有問題，為什么在測試庫運行又是正常的呢？

近期產(chǎn)品中的這條SQL也沒有做過修改，為什么以前是正常的，現(xiàn)在卻突然出現(xiàn)問題了呢？

沒有騙你喲，用戶發(fā)來了測試庫上執(zhí)行后結果正確的截圖。

剛開始看到這個現(xiàn)象，也是很納悶，會不會是數(shù)據(jù)庫的什么參數(shù)影響了排序，像經(jīng)常遇到的"_gby_hash_aggregation_enabled"這個參數(shù)對Group By排序的影響（后續(xù)可能會寫一個這方面的案例），問題是這個SQL里沒有Group by子句。

從數(shù)據(jù)庫的基礎理論方面想了想，影響排序的還有哪些因素呢？

如果是有索引，那么索引本身就是排了序的，讀取數(shù)據(jù)時就不需要排序了，再用Rownum取值，是不是就可以取到預期的結果了呢？

也就是說，變相實現(xiàn)了先排序，后取序號的作用。

為了證實這一點，在公司的測試庫上做了一個驗證：

1. 相同的SQL，執(zhí)行結果跟用戶測試庫的結果是一樣的，序號正常排序。

2. 禁用主鍵“病案項目_PK”(以“編碼”字段為索引)后，執(zhí)行結果跟用戶正式庫的結果一樣，序號排序就亂了，再現(xiàn)了問題。

alter table病案項目 disable constraint病案項目_PK;

恢復主鍵后，序號就正常排序了。

alter table病案項目 enable constraint病案項目_PK;

是不是用戶生產(chǎn)庫的主鍵被禁用了，索引丟了？

轉移歷史數(shù)據(jù)期間，的確會禁用一些約束，但是這張表并不是轉出相關的表呀！而且轉完數(shù)據(jù)后，我們恢復約束后也做過檢查。

會不會是用戶后期運行過程中，某種原因導致該索引無效了呢？

馬上查詢用戶的生產(chǎn)庫，主鍵是有效的，索引也是有效的。

眼看找到一條路，沒想到走到底發(fā)現(xiàn)是個死胡同，不要懈氣，既然問題再現(xiàn)了，原理也清楚了，順著這條路，仔細找找，一定有出路。

在測試環(huán)境，對比分析一下，禁用主鍵（刪除“編碼”字段的索引）前后的執(zhí)行計劃。

對比發(fā)現(xiàn)了差異：

有索引時，執(zhí)行計劃包含” INDEX FULL SCAN”，沒有排序操作。

沒有索引時，執(zhí)行計劃包含” TABLE ACCESS FULL”，有排序操作“SORT ORDER BY”。

用戶生產(chǎn)庫的執(zhí)行計劃是什么呢？

一查詢，結果跟我在這邊測試環(huán)境下的執(zhí)行計劃一樣，走了全表掃描。

為什么沒有走索引全掃呢？

會不會是統(tǒng)計信息收集有問題，導致成本評估時，認為全表掃描的成本更低，所以選擇了它呢。

在用戶生產(chǎn)庫收集了一下統(tǒng)計信息，結果就正常了，執(zhí)行計劃變成了” INDEX FULL SCAN”。

表的統(tǒng)計信息丟失的原因，不得而知。

其實解決問題的辦法還有其他的，例如：通過Sql Profile加提示字指定索引。

既然是統(tǒng)計信息沒有收集的問題，那是不是可以再現(xiàn)一下問題現(xiàn)象呢？

完全可以。

我們刪除統(tǒng)計信息后來看看，是不是就再現(xiàn)用戶生產(chǎn)庫的場景了呢？

exec dbms_stats.delete_table_stats(ownname => 'ZLHIS',tabname => '病案項目');

再PLSQL中查看剛才那條SQL的執(zhí)行計劃，確實從索引全掃描變成了全表掃描。

執(zhí)行SQL，查詢一下數(shù)據(jù)，咦，怎么數(shù)據(jù)不是亂序的？

執(zhí)行計劃不是變了嗎？

什么情況？

又到強調(diào)掌握數(shù)據(jù)庫基礎理論的重要性的時候了，如果只學習那些用得到的知識，書到用時方恨少，只有打開堅實的基礎，才能在實戰(zhàn)中臨場應對。

清空一下共享池再看看。

alter system flush SHARED_POOL;

再次執(zhí)行數(shù)據(jù)查詢，哈哈，這次，“編碼”字段終于亂序了，一陣竊喜。

數(shù)據(jù)亂了還要高興，什么原因？因為它如你所愿，你看到了想要的結果。

為什么之前從PLSQL看到執(zhí)行計劃是全表掃描，但查詢數(shù)據(jù)卻跟索引全掃描的結果一樣呢？

別忘了，PLSQL只是一個工具，它的F5查看執(zhí)行計劃的功能，不知道騙了多少人，坑了多少人。

其實你明白它查看執(zhí)行計劃的原理，就知道它不是真的想騙你，如果你有興趣，可以通過10046去探個究竟，因為篇幅有限，這里就不再詳述。

總結：

至此，這個問題搞清楚了，我們再來回看一下SQL，重新梳理一下：

select rownum as 序號,編碼,名稱,內(nèi)容 from 病案項目 order by 編碼

當Order by和Rownum在同一層次時，排序是最后執(zhí)行的，先取行號再排序，這樣就無法根據(jù)排序結果得到正確行號，如果能夠避免排序，就能得到期望的結果。

當Order by中的字段是非空索引時（主鍵索引屬于非空索引），如果統(tǒng)計信息收集正確，執(zhí)行計劃評估成本后，就會選擇“索引全掃描”，由于索引本身是排了序的，就能避免排序。

當表的統(tǒng)計信息丟失，優(yōu)化器在評估成本時，由于缺乏統(tǒng)計信息作為成本計算的依據(jù)，它就會選擇“全表掃描”作為執(zhí)行計劃，然后再排序。

為了避免統(tǒng)計信息收集導致的這種問題，在各種用戶環(huán)境下得到穩(wěn)定的結果，這個SQL可以改為：

Select Rownum As 序號, 編碼, 名稱, 內(nèi)容 From (Select 編碼, 名稱, 內(nèi)容 From 病案項目 Order By 編碼)

在子查詢中先排序，外層查詢中再取Rownum，類似的，當我們在寫Rownum<5這類SQL時，也需要注意排序的問題，應該先在子查詢中寫排序，外面再限制返回行數(shù)。

思考：

為了加深對這個知識的理解，你可以試一下這兩個SQL：

Select姓名 From人員表 Where Rownum < 2 Order By建檔時間 Desc;

Select姓名 From人員表 Where Rownum < 2 Order By ID Desc;

ID是主鍵，順序遞增，建檔時間最大的記錄，其ID最大。

為什么兩個SQL得到的數(shù)據(jù)不樣呢？對比一下執(zhí)行計劃看看。

知識點的擴展應用：

利用這個案例中的知識點：排序字段如果與非空索引的字段順序相同，則可以利用索引本身已排序的特性來避免排序，這在一些高并發(fā)的高頻SQL中，對系統(tǒng)的整體性能提升將會起到非常重要的作用，因為排序操作對cpu消耗比較大，特別是那些大的、高頻的排序。

特別提醒，兩個重要條件：字段順序相同，非空索引。

------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
公眾號：醫(yī)信系統(tǒng)性能優(yōu)化
主要寫一些日常工作中性能優(yōu)化方面的案例，包括SQL優(yōu)化，數(shù)據(jù)結構設計優(yōu)化，Oracle系統(tǒng)性能優(yōu)化。
面向編寫SQL及相關腳本的開發(fā)人員和技術支持人員，分享一些性能優(yōu)化的經(jīng)驗。
對性能優(yōu)化技術學習感興趣的同學，歡迎訂閱，共同學習，相互交流。

本文名稱：Rownum和OrderBy的執(zhí)行順序造成的影響
標題來源：http://www.muchs.cn/article2/geesic.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、品牌網(wǎng)站設計、微信公眾號、App設計、定制網(wǎng)站、用戶體驗

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容