分类： Oracle
2013-02-16 14:29:40
前面说过一个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%’的数据范围也很大，因此根据局部范围扫描性能策略矩阵可知，当前启用局部范围数据扫描后性能会得到很大提升。
