作者介紹
羅敏，從事oracle技術研究、開發和服務工作20餘年，在oracle中國公司的10多年，分别在顧問咨詢部、技術服務部擔任資深技術顧問。曾參與國内銀行、電信、政府等多個行業大型it系統的建設和運維服務工作，為國内主要軟體開發商和內建商進行過多場oracle進階技術應用教育訓練和交流活動。著有書籍《品悟性能優化》、《感悟oracle核心技術》、《oracle資料庫技術服務案例精選》。
今天本人不妨對一條不太複雜的sql語句在技術上進行深入剖析，與大家共同分享其中的實施經驗和實施方法，更對其中折射出的一些深層次問題發表些許感悟。
1一條不太複雜的sql語句
這是一條來自某行業資料倉庫系統的sql語句：
select /*+ parallel(4) */
a.nsrzhdah, a.sssqq, a.sssqz, a.nsrsbh
from j1_ldm.ldmt02_ye_sbxx a, j1_di.di_zsxm b
where a.sbqx >= '20160101'
and a.sbqx <= '20160331'
and a.gzlx_dm in ('1', '2')
and a.zsxm_dm = b.zsxm_dm
and b.sfbz_dm = '1'
and (a.sbrq > a.sbqx or sbrq is null)),
這是該語句現有的執行計劃：
該語句現有執行時間為5分58秒，邏輯讀為386750，實體讀為130678 。
上述執行計劃中oracle對表ldmt02_ye_sbxx表是按sbqx字段索引進行通路，而di_zsxm是一個代碼表，該表進行全表掃描非常正常。進一步，我發現ldmt02_ye_sbxx表按sbrq（申報日期）字段按月進行了分區，而sbqx（申報期限）字段索引是按月進行global range partition分區的分區索引。另外，對這兩個表的通路oracle均采用了并行處理技術。
該語句現有執行計劃似乎很正常，也就是好像沒什麼優化空間。查詢2006年1月1日至2016年3月31日3個月的交易資料，需要将近6分鐘在業務上似乎也很正常。
2全表掃描居然比索引通路快！
盡管看似正常，但我還是想嘗試優化的可能性。首先，我發現針對這種查詢3個月交易資料的典型的大批量資料通路語句，開發人員強制使用并行處理的政策是非常正确的。但是，開發人員的hint: /*+ parallel(4) */ 編寫方式得有點另類，那就是為什麼隻寫并行度（dop）為4，而不寫表名呢？于是我嘗試将該語句hint修改為：/*+ parallel(a,4) */。再觀察執行計劃，奇怪的事情發生了：
原來，oracle優化器此時對ldmt02_ye_sbxx表的通路不再走sbqx字段索引，而改走全表掃描了！語句實際執行效果呢？執行時間為2分52秒，速度提高1倍！但是邏輯讀為651387，實體讀為620747。也就是說，盡管資源開銷更大，但針對資料倉庫應用，oracle全表掃描速度比按索引通路更快！
3進一步優化空間
盡管執行時間更快了，但針對幾千萬、甚至幾億條記錄的交易明細表進行全表掃描總不是一種好政策，而且資源消耗的确更大了，畢竟該語句隻是查詢3個月的資料。
假設将該表調整為按sbqx（申報期限）字段進行月分區，oracle将采用分區裁剪功能，也就是該語句隻查詢3個月的分區資料，性能一定能大幅度提升！不僅預計響應速度在1分鐘之内，甚至秒級，而且邏輯讀、實體讀都将大幅度下降。
可惜我在現場工作時間有限，也無法在生産或測試環境進行這種調整分區政策的大動幹戈的驗證，但略有經驗的資料庫設計和開發人員都知道這種優化政策的效果是顯而易見的。
4原來還是對分區技術了解不夠
當我向應用開發人員提出上述分區表改造建議時，他們也說出了他們的設計初衷：原來他們是考慮應用主要是通過sbrq（申報日期）字段進行查詢，是以就考慮按sbrq（申報日期）字段進行分區設計了。同時，他們也對我的優化方案表示了擔憂：如果改成按sbqx（申報期限）字段進行分區，那按sbrq（申報日期）字段進行查詢的sql語句是不是就性能下降了？這就是很多資料庫設計和應用開發人員的一個誤區：以為sql語句按哪個字段做條件多，就一定要按這個字段進行分區。
究其根源，設計者和開發人員還是對oracle的分區技術，尤其是分區索引技術的了解不深入。回到該問題，盡管表按sbqx（申報期限）進行分區了，sbrq（申報日期）字段仍然可以建成分區索引，包括global range partition索引或者local non-prefixed partition索引，依然可以提高按sbrq（申報日期）字段進行通路的效率。這就是這兩種索引結合該案例的示意圖：
在global range partition索引情況下，oracle優化器能保證隻通路一個索引子分區，通路效率仍然會有提升。但是，一旦出現分區管理操作（drop、split、merge等），将導緻global range partition索引失效，也就是導緻業務連續性下降。
在local non-prefixed partition索引情況下，不會出現索引失效情況，也就是業務連續性尚可，但除非在sql語句中增加分區字段sbqx（申報期限）的條件，否則，很可能導緻該索引全掃描，性能反而會下降。
綜合利弊，尤其是考慮sbqx（申報期限）和sbrq（申報日期）兩個字段本身的業務邏輯，sbqx（申報期限）儲存的是月資料，是更宏觀的字段，而sbrq（申報日期）儲存的是天資料，是更微觀的字段。顯然，将j1_ldm.ldmt02_ye_sbxx表按sbqx（申報期限）字段進行按月範圍分區，更符合業務邏輯。
5感悟1：資料倉庫應用技術運用的針對性和合理性
通過上述這個并非複雜的sql語句的深入剖析，我想首先感悟的就是：廣大應用開發人員在開發資料倉庫應用時技術運用一定要有針對性，同時也要保證技術運用的合理性。
衆所周知，it系統總體上可分為聯機交易系統（oltp）和聯機分析系統（olap）兩類，olap系統也可稱之為資料倉庫系統。這兩類系統無論在業務特征還是适用的技術方面都迥然不同。對oltp應用，具有并發量大、單筆事務處理的資料量小等特點，應以系統的響應速度作為首要的優化目标。而olap應用則具有大批量資料處理、并發事務低特點，應該以系統整體吞吐量作為優化目标。olap系統在具體技術運用政策方面，應該貫徹大批量、并行處理思路，并合理運用oracle并行處理、分區操作、hash-join、merge語句、資料倉庫函數、外部表、位圖索引、物化視圖等典型技術。
索引技術其實更适合于oltp系統中的小事務處理，而針對該案例查詢3個月資料的大批量資料處理，全表或全分區掃描加并行處理則是更适合的技術政策了。回到該案例，當我告訴應用開發人員全表掃描加并行處理比索引通路更快時，令他們十分驚訝。
再者，分區技術既适合于oltp系統，也适合于olap系統，但是針對這兩類系統，分區技術運用政策和實作目标是不同的。我們應該深入分析應用需求特點，結合oracle豐富的分區技術，有針對性地選擇分區字段，并合理設計分區索引。例如，若隻考慮性能，則将sbrq（申報日期）設計為global range partition索引是合理的。若作為資料倉庫系統，未來需要按sbqx（申報期限）按月進行分區的曆史資料管理操作，則sbrq（申報日期）設計為local non-prefixed partition索引，将不會出現索引失效情況，也就是業務連續性将更高。同時，針對資料倉庫應用具有按時間進行通路和分析的特點，盡量建議在語句中增加分區字段sbqx（申報期限）條件，這樣性能也能得到保證。若這樣設計就更全面、更合理了。
6感悟2：業務與技術結合的重要性
通過該案例，大家也一定能感悟到這一點，那就是業務與技術結合的重要性。我想這也正是國内it行業多年來一直存在的一個痼疾：一方面，業務人員包括應用開發人員盡管非常熟悉應用邏輯，但對相關底層技術的确缺乏深入、細緻的了解，在技術運用方面的确存在明顯的誤區和不足，例如本案例中應用開發人員就是對oracle的各種分區技術，尤其是各種分區索引技術的原理、适用場景等缺乏全面、深入的了解。而另一方面，熟悉底層技術的人員，又不願意去關注業務邏輯和實際需求。總之，這就是典型的業務和技術的脫節問題。
這種局面的存在導緻什麼後果呢？這就是it系統普遍存在的粗糙、性能低下、資源消耗過大等問題。繼續回到該案例，無論是客戶，還是技術人員一定隻是感受到應用響應速度慢、i/o資源消耗太大等表面現象，進而簡單地認為是存儲系統效率太低，甚至感覺是oracle對海量資料倉庫系統處理能力有限，需要給系統瘦身，進行曆史資料遷移，進而進一步加劇整個系統架構的複雜性。設計開發人員甚至以現在流行分庫設計為理由，欲将該資料倉庫大卸八塊，進行拆庫操作，殊不知都是資料庫設計和應用開發本身的問題，把庫拆小了，隻是緩解了問題，并沒有從根本上解決問題。
業務與技術的有機結合是何等重要！
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-09-14</b>