親愛(ài)的DBA同胞們遵馆，你們是否記得在你找工作時(shí)，印象最深刻的面試題呢赦肃？那些看似簡(jiǎn)單的題目溅蛉，實(shí)則蘊(yùn)藏很大的玄機(jī)。今天我們通過(guò)一道經(jīng)典的 ORacle DBA面試題目他宛，去發(fā)現(xiàn)我們?cè)诿嬖囍写啵降走€缺少那些能力？
這道題看起來(lái)很簡(jiǎn)單厅各，然而镜撩，90%的面試者都不知道答案。队塘。袁梗。
面試題描述：
對(duì)于一個(gè)NUMBER(1)的列，查詢中的WHERE條件如果分別是大于3和大于等于4憔古，二者是否等價(jià)遮怜。
乍一看，這個(gè)問(wèn)題并不難鸿市。請(qǐng)讀者朋友們?cè)诶^續(xù)讀下文之前锯梁，用30秒的時(shí)間思考即碗。
接下來(lái)我們通過(guò)楊長(zhǎng)老的博客，來(lái)說(shuō)明面試者在這道題目中給你挖下了那些坑陌凳。首先對(duì)于查詢結(jié)果而言剥懒，二者沒(méi)有任何區(qū)別。從這一點(diǎn)上講無(wú)論是指定大于3還是指定大于等于4合敦，二者結(jié)果都是一樣的初橘。
但是，結(jié)果集一樣并不代表二者等價(jià)蛤肌。
在Check約束下壁却，二者的執(zhí)行計(jì)劃是不一樣的
首先我們來(lái)看二者的執(zhí)行計(jì)劃：
如果表中恰好有上面的CHECK約束，可以發(fā)現(xiàn)裸准，對(duì)于大于3和大于等于4這兩個(gè)SQL的執(zhí)行計(jì)劃是不一致的展东。
對(duì)于后者，由于查詢的條件違反了CHECK約束炒俱，因此Oracle在執(zhí)行計(jì)劃前面增加了一個(gè)FILTER盐肃，使得整個(gè)查詢不需要在執(zhí)行，因此這個(gè)查詢不管表中數(shù)據(jù)有多少权悟，都會(huì)在瞬間結(jié)束砸王。
而對(duì)于大于3這種情況，雖然根據(jù)CHECK的約束和列定義峦阁，可以推斷出這條查詢不會(huì)返回任何記錄谦铃，但是Oracle的優(yōu)化器并沒(méi)有聰明到根據(jù)列的精度來(lái)進(jìn)行分析，因此這個(gè)查詢會(huì)執(zhí)行全表掃描榔昔。
也就是說(shuō)驹闰，雖然這兩個(gè)查詢的最終結(jié)果一樣，但是執(zhí)行計(jì)劃并不相同撒会，而且對(duì)于大表而言嘹朗，這種情況下性能也有較大的差別。
當(dāng)然這種CHECK約束是特例的情況诵肛，一般情況下不會(huì)出現(xiàn)屹培。原則上到底是選擇大于3還是大于等于4，應(yīng)該根據(jù)具體的業(yè)務(wù)來(lái)決定怔檩，而不要嘗試?yán)肙racle的數(shù)據(jù)精度來(lái)設(shè)置查詢條件褪秀。如果以后一旦字段的結(jié)構(gòu)發(fā)生了修改，比如這個(gè)例子中字段的允許出現(xiàn)小數(shù)薛训，那么這兩個(gè)SQL的WHERE條件就不再等價(jià)了溜歪。
在有索引的情況下，二者的性能是否有差異
有朋友認(rèn)為许蓖，如果是大于3的話，走索引掃描，會(huì)從3開(kāi)始掃描膊爪，而大于等于4自阱，會(huì)從4開(kāi)始掃描。
根據(jù)這個(gè)觀點(diǎn)對(duì)于大于3的情況米酬，Oracle會(huì)先找到索引鍵值為3的塊沛豌，然后開(kāi)始掃描。而對(duì)于大于等于4的情況赃额，則會(huì)找到索引鍵值為4的塊加派，然后執(zhí)行掃描。如果表中包含大量索引鍵值為3的記錄跳芳，這兩個(gè)查詢就可能存在性能上的差異芍锦。
看一個(gè)具體的例子：
創(chuàng)建一張測(cè)試表，表中所有的ID都等于3飞盆，下面插入一條ID為4記錄：
下面執(zhí)行查詢語(yǔ)句：
可以看到娄琉，無(wú)論是執(zhí)行時(shí)間，還是邏輯讀吓歇，兩個(gè)SQL沒(méi)有任何的差別孽水。為了更好的證明Oracle并沒(méi)有讀取ID等于3的記錄，執(zhí)行下面的查詢：
其實(shí)看過(guò)Concept對(duì)索引結(jié)構(gòu)有一定了解就知道城看，根據(jù)Oracle索引結(jié)構(gòu)的特點(diǎn)女气，無(wú)論是大于3還是大于等于4，二者的查詢是掃描的葉節(jié)點(diǎn)都是同一個(gè)测柠，因此不會(huì)在這一點(diǎn)上不會(huì)存在性能的差別炼鞠。
在使用物化視圖上的差別
如果表上建立了可查詢重寫(xiě)的物化視圖，兩個(gè)查詢?cè)谑欠袷褂梦锘晥D上有所差別鹃愤。
例子仍然使用T_NUM表：
先構(gòu)造一張大表簇搅，現(xiàn)在分別執(zhí)行兩個(gè)查詢，檢查執(zhí)行結(jié)果：
由于采用的都是全表掃描软吐，二者執(zhí)行的時(shí)間和邏輯讀完全一樣瘩将。
下面建立一個(gè)物化視圖：
下面檢查系統(tǒng)設(shè)置是否滿足查詢重寫(xiě)：
從執(zhí)行計(jì)劃可以看到，對(duì)于大于等于4的情況凹耙，Oracle直接掃描了物化視圖了姿现。而對(duì)于大于3的情況，Oracle同時(shí)掃描了物化視圖和原表肖抱，顯然效率比較低备典。
這個(gè)例子其實(shí)和第一個(gè)例子很類(lèi)似。雖然根據(jù)字段類(lèi)型可以判斷出大于3和大于等于4是等價(jià)的意述，但是對(duì)于CBO來(lái)說(shuō)提佣，并不會(huì)將數(shù)據(jù)類(lèi)型的因素考慮進(jìn)去吮蛹。因此導(dǎo)致兩個(gè)查詢?cè)谑褂梦锘晥D時(shí)執(zhí)行計(jì)劃的區(qū)別。
總結(jié)一下兩者的區(qū)別
1拌屏、在Check約束下潮针，前者會(huì)執(zhí)行全表掃描，后者經(jīng)過(guò)check約束的檢查后倚喂，通過(guò)filter結(jié)束查詢每篷，能夠更高效地返回結(jié)果；
2端圈、在使用索引的時(shí)候焦读，由于Oracle索引結(jié)構(gòu)的特點(diǎn)，兩者掃描的節(jié)點(diǎn)都是從4開(kāi)始舱权，在執(zhí)行計(jì)劃和邏輯讀矗晃，執(zhí)行時(shí)間等各方面都不存在性能差異；
3刑巧、在使用物化視圖的過(guò)程中喧兄，前者同時(shí)掃描物化視圖和原表，效率較低啊楚。后者直接掃描物化視圖吠冤。
