864
技術社區[雲棲]
必讀推薦- 90%的麵試者都不知道這道題的答案
親愛的DBA同胞們，你們是否記得在你找工作時，印象最深刻的麵試題呢？那些看似簡單的題目，實則蘊藏很大的玄機。今天我們通過一道經典的 ORacle DBA麵試題目，去發現我們在麵試中，到底還缺少那些能力？
這道題看起來很簡單，然而，90%的麵試者都不知道答案。。。
麵試題描述：
對於一個NUMBER(1)的列，查詢中的WHERE條件如果分別是大於3和大於等於4，二者是否等價。
乍一看，這個問題並不難。請讀者朋友們在繼續讀下文之前，用30秒的時間思考。
接下來我們通過楊長老的博客，來說明麵試者在這道題目中給你挖下了那些坑。首先對於查詢結果而言，二者沒有任何區別。從這一點上講無論是指定大於3還是指定大於等於4，二者結果都是一樣的。
但是，結果集一樣並不代表二者等價。
在Check約束下，二者的執行計劃是不一樣的
首先我們來看二者的執行計劃：
如果表中恰好有上麵的CHECK約束，可以發現，對於大於3和大於等於4這兩個SQL的執行計劃是不一致的。
對於後者，由於查詢的條件違反了CHECK約束，因此Oracle在執行計劃前麵增加了一個FILTER，使得整個查詢不需要在執行，因此這個查詢不管表中數據有多少，都會在瞬間結束。
而對於大於3這種情況，雖然根據CHECK的約束和列定義，可以推斷出這條查詢不會返回任何記錄，但是Oracle的優化器並沒有聰明到根據列的精度來進行分析，因此這個查詢會執行全表掃描。
也就是說，雖然這兩個查詢的最終結果一樣，但是執行計劃並不相同，而且對於大表而言，這種情況下性能也有較大的差別。
當然這種CHECK約束是特例的情況，一般情況下不會出現。原則上到底是選擇大於3還是大於等於4，應該根據具體的業務來決定，而不要嚐試利用Oracle的數據精度來設置查詢條件。如果以後一旦字段的結構發生了修改，比如這個例子中字段的允許出現小數，那麼這兩個SQL的WHERE條件就不再等價了。
在有索引的情況下，二者的性能是否有差異
有朋友認為，如果是大於3的話，走索引掃描，會從3開始掃描，而大於等於4，會從4開始掃描。
根據這個觀點對於大於3的情況，Oracle會先找到索引鍵值為3的塊，然後開始掃描。而對於大於等於4的情況，則會找到索引鍵值為4的塊，然後執行掃描。如果表中包含大量索引鍵值為3的記錄，這兩個查詢就可能存在性能上的差異。
看一個具體的例子：
創建一張測試表，表中所有的ID都等於3，下麵插入一條ID為4記錄：
下麵執行查詢語句：
可以看到，無論是執行時間，還是邏輯讀，兩個SQL沒有任何的差別。為了更好的證明Oracle並沒有讀取ID等於3的記錄，執行下麵的查詢：
其實看過Concept對索引結構有一定了解就知道，根據Oracle索引結構的特點，無論是大於3還是大於等於4，二者的查詢是掃描的葉節點都是同一個，因此不會在這一點上不會存在性能的差別。
在使用物化視圖上的差別
如果表上建立了可查詢重寫的物化視圖，兩個查詢在是否使用物化視圖上有所差別。
例子仍然使用T_NUM表：
先構造一張大表，現在分別執行兩個查詢，檢查執行結果：
由於采用的都是全表掃描，二者執行的時間和邏輯讀完全一樣。
下麵建立一個物化視圖：
下麵檢查係統設置是否滿足查詢重寫：
從執行計劃可以看到，對於大於等於4的情況，Oracle直接掃描了物化視圖了。而對於大於3的情況，Oracle同時掃描了物化視圖和原表，顯然效率比較低。
這個例子其實和第一個例子很類似。雖然根據字段類型可以判斷出大於3和大於等於4是等價的，但是對於CBO來說，並不會將數據類型的因素考慮進去。因此導致兩個查詢在使用物化視圖時執行計劃的區別。
總結一下兩者的區別
1、在Check約束下，前者會執行全表掃描，後者經過check約束的檢查後，通過filter結束查詢，能夠更高效地返回結果；
2、在使用索引的時候，由於Oracle索引結構的特點，兩者掃描的節點都是從4開始，在執行計劃和邏輯讀，執行時間等各方麵都不存在性能差異；
3、在使用物化視圖的過程中，前者同時掃描物化視圖和原表，效率較低。後者直接掃描物化視圖。
原文發布時間為：2017-09-11
本文作者：楊廷琨
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲”微信公眾號
最後更新：2017-09-11 09:33:07