?解析Oracle數據掃描——Oracle SQL查詢優化(2) | 鄭州計算機學校_鄭州電腦學校_鄭州電腦培訓

  2、提高局部范圍數據掃描執行性能的原理:  2.1 查詢條件的類別與作用:  當我們發起一個帶有條件的查詢SQL語句時,通常會賦予該語句多個查詢條件,在這些查詢條件中,通常會有一個或者幾個查詢條件會作為數據檢索的發起者,這些作為發起者的查詢條件稱為“驅動查詢條件”;同時那些不作為數據檢索發起者的查詢條件,在整個查詢數據的過程中,只會起到過濾由驅動查詢條件所決定的數據范圍內數據的作用,這些查詢條件就稱為“過濾查詢條件”。  由此可見驅動查詢條件和過濾查詢條件,一個起到查詢發起者的作用,一個起到數據過濾者的作用,二者操作的目標數據集是不同的,驅動查詢條件查詢的是整體數據集,而過濾查詢條件查詢的是由驅動查詢條件獲取的結果集。由此可見驅動查詢條件對于最終的查詢語句的執行性能,起到了至關重要的作用。因為他們決定了最終查詢結果的最大數據范圍,以及能否最快的獲取這個范圍內的數據,而過濾查詢條件只是從驅動查詢條件決定的數據范圍內進行數據過濾。當然我們也不能忽視通過過濾查詢條件進行數據過濾對查詢性能的影響,但是畢竟它沒有對最終性能起到主要作用。  通常選擇哪個查詢條件作為驅動查詢條件是由執行優化器和優化器運行模式(FIRST_ROWS或者ALL_ROWS)決定的。Oracle在決定驅動查詢條件時,通常會考慮使用具有索引的列作為驅動查詢條件,一般情況下Oracle會評估個查詢條件上是否具備主鍵索引、唯一索引、B*Tree或其他類型索引、以及是否是使用等值條件的復合索引的手字段等,同時還會基于優化器模式通過成本估算方法評估各種索引的成本效能,盡力從中識別出最為高效的索引并將其作為驅動查詢條件。如果查詢優化器發現存在多個高效索引,那么理論上優化器會使用自己的策略決定出最佳執行成本的驅動查詢條件;同時查詢優化器還會識別出,哪些本應用于過濾條件的索引項,雖然不是那么高效,但是對驅動查詢條件檢索數據會提供很好的幫助,那么優化器會將這些索引與驅動查詢條件合并執行,進而期望得到最好的查詢性能。  但是通過查詢優化器來決定驅動查詢條件時,通常情況下都是沒問題的,但是有些時候由于數據分布、數據對象統計信息過舊,以及與我們期望的數據掃描方式不符等問題,不得不需要我們人為去干預上述優化器的決策過程,其中最常用的干預手段就是使用Hints。這也從一個側面說明,當今人工智能還無法取代真正的人類智能。  2.2 提升范圍掃描性能的原則:  有了上述查詢條件類別的知識,我們就可以來討論提升數據局部范圍掃描性能的原則了。即使從常識出發來思考,也可以知道從較小的數據集中查詢數據的執行速度會比較高效,從大范圍的數據集中查詢數據會比較低效。但是一旦融入了上述驅動查詢條件和過濾查詢條件的相互作用,在局部范圍掃描時就會發現,從較大范圍中查詢數據的輸出速度反倒高于從較小范圍中查詢數據。為什么會得到這樣與我們常識相反的結果呢?我們通過下面的例子來說明這個問題。  這里有一個普通的SQL查詢語句Select * from order where ordno between 1and 1000 and custno like ‘TB%’;  假設在ordno和custno字段上都建有索引,同時知道滿足ordno查詢條件的數據記錄有1000行,滿足custno的數據記錄有10行。很明顯由于查詢條件都具有索引,而且無需排序、分組、匯總等操作,因此該語句會使用局部范圍數據掃描,那么就可能出現如下執行路徑:  (1)使用ordno作為驅動查詢條件,custno作為過濾查詢條件:  此時查詢會使用ordno索引掃描數據滿足條件的數據行,同時使用custno過濾條件來過濾滿足條件的數據行,并將過濾出的符合條件的數據行填充到批量數組中,作為查詢結果返回。但是問題是滿足ordnobetween 1 and 1000條件的記錄行有1000行,而滿足custnolike ‘TB%’條件的記錄行只有10行,那么用只有10行記錄的數據去過濾擁有1000行記錄的數據,并填充容納最終結果的批量數組,可想而知批量數組很難被快速填滿,即查詢結果很難快速被返回,最壞的情況下可能要從頭到尾的掃描驅動查詢條件所決定的全部數據范圍(1000條記錄)后查詢才能結束。  (2)使用custno作為驅動查詢條件,使用ordno作為過濾查詢條件:  此時查詢使用custno索引掃描滿足條件的數據行,同時使用ordno過濾條件來過濾滿足條件的數據行,并將過濾出的符合條件的數據行填充到批量數組中,作為查詢結果返回。這時由于滿足ordnobetween 1 and 1000條件的記錄行有1000行,那么很容易從中找到能夠匹配滿足custnolike ‘TB%’條件的10行記錄,因此批量數組很容易就能被填滿,即使最壞的情況也最多只會掃描滿足custnolike ‘TB%’條件的10行記錄,因此此時查詢結果接能夠快速的返回。  為了改進查詢性能,我們可以使用交換查詢條件角色的方式,即交換驅動查詢條件和過濾查詢條件,因此我們可以使用下面的語句來改寫上述查詢:Select /*+ index(order custno_inx) */ *From order where ordnobetween 1 and 1000 and custno like ‘TB%’;  我們通過Hints來影響優化器行為,使優化器制定出從使用custno字段上的索引來發起查詢,即將驅動查詢條件變成custnolike ‘TB%’,將過濾查詢條件變成ordno between 1 and 1000。這里使用了Hints,其實還可以使用一些措施使得ordno索引失效,也可以達到同樣的目的,比如在ordno字段上使用一個對查詢結果不會造成影響的函數,如rtrim(ordno)between1 and 1000,但這種方法存在弊端,它可能會影響優化器制定出合并使用custno和ordno共同來進行數據查詢的執行計劃。  從這個示例中我們可以看出提升局部范圍掃描執行性能的策略有以下兩點:  ● 盡量縮減驅動查詢條件的數據范圍,以減少數據掃描量;  ● 盡量擴大過濾查詢條件的數據范圍,以增加填滿批量數組快速返回查詢結果的概率;  由此我們可以得到下述局部范圍掃描性能提升策略描述矩陣:滿足驅動查詢條件的數據范圍滿足過濾查詢條件的數據范圍性能措施小小高小大高大小低交換過濾條件的角色大大高相關鏈接:解析Oracle數據掃描——Oracle SQL查詢優化(1)

6十1开奖黑龙江