解析Oracle資料掃描 Oracle SQL查詢最佳化 引導局部範圍資料掃描的方法（1）

設法改變訪問路徑

       前面說過一個SQL語句如果進行引發排序的操作，通常會進行全部範圍掃描。如下面的SQL語句：
Select * from product where item_cd like ‘AB%’ and category like ‘C%’
order by item_cd desc;
由於存在排序操作，因此無法進行局部範圍掃描。但是我們知道，如果在欄位item_cd上建立有索引的話，那麼就可以利用索引是排好序的特性，來避免order by操作，從而避免排序的發生進而引導最佳化器進行局部範圍資料掃描。
但是在CBO最佳化器下，即使item_cd欄位上存在索引，由於CBO成本模型對索引的評估，我們也無法確保進行局部範圍掃描來避免order by操作。因此就必須使用能夠確保使用索引掃描資料，同時又能避免排序操作的Hints。因此可以如下改寫原SQL語句：
Select /* +index_desc(product item_index)*/ *
From product
Where item_cd like ‘AB%’ and category like ‘C%’;
       通過應用Hints來保證最佳化器針對item欄位上的item_index索引進行倒序掃描，這樣可以利用索引的有序性，同時因為是倒序掃描，所以可以直接擷取資料並填充批量數組，而避免了因為要進行倒序排序，而進行的結果集的二次處理，即避免了全域範圍掃描，啟用了局部範圍掃描。
但是如果需要針對item_cd和category欄位進行排序，那麼由於在兩個欄位上的查詢條件都是like因此又無法進行局部範圍掃描了。此時可以將item_cd和category欄位建成一個複合索引來實現局部範圍掃描。
上述語句屬於驅動查詢條件和排序條件一致的情況，但在實際工作中還會經常遇到，驅動查詢條件和排序欄位不一致的情況。如下面的語句：
Select ord_dept,ordqty*100 from order where ord_date like ‘2005%’
Order by ord_dept desc;
此處驅動查詢條件是ord_date，排序欄位是ord_dept，因此此時無法進行局部範圍掃描。此時可以通過改變查詢條件的角色來實現局部範圍掃描，如下所示：
Select/*+ INDEX_DESC(a ord_dept_index) */
Ord_dept,ordqty*100
From product
Where ord_date like ‘2005%’ and ord_dept>’’;
通過條件ord_dept>’’實現驅動查詢條件與排序欄位相同，而且通過使用ord_dept欄位的索引來實現局部範圍掃描，此時ord_dept變成驅動查詢條件，而且可知滿足該驅動查詢條件的範圍很大，同時滿足現在過濾條件ord_date like ‘2005%’的資料範圍也很大，因此根據局部範圍掃描效能策略矩陣可知，當前啟用局部範圍資料掃描後效能會得到很大提升。