区间检索SQL优化一则

有这样一个case：根据给定的一个ip地址，找到对应的国家。表中存储的是IP地址段：起始IP和结束IP，每个IP只对应其中的一条记录。

这个case在电信的号码段检索应用中同样适用！

原SQL如下：

Select country_code
From COUNTRY_IP_RANGE IP 
WHERE IP.Start_Ip1 <= ip_to_number1(:ip)     
AND IP.End_Ip1 >= ip_to_number1(:ip);

COUNTRY_IP_RANGE IP表上有Start_Ip1、End_ip两个字段的联合索引： idx_lin。

问题：要查找的ip地址值越大，消耗资源（consistent gets ）就越大

exec :ip:= '1.0.0.1';
SQL
4  consistent gets
0  physical reads


exec :ip:= '222.252.0.123';
SQL
121449  consistent gets

解决方法：

第一步：根据业务逻辑，SQL可以增加一个rownum=1的谓词条件

Select country_code
From COUNTRY_IP_RANGE IP 
WHERE IP.Start_Ip1 <= ip_to_number1(:ip)     
AND IP.End_Ip1 >= ip_to_number1(:ip) AND rownum=1;

但是仍没有实质的性能改善。

第二步：使用index_rs_desc 索引降序扫描的hint

select /*+ INDEX_RS_DESC(ip idx_lin) */country_code
from COUNTRY_IP_RANGE IP 
WHERE IP.Start_Ip1 <= ip_to_number1(:ip)     
AND IP.End_Ip1 >= ip_to_number1(:ip)
And rownum=1;

这时，不管ip变量值的大小如何变化，consistent gets都只有3.

总结：

  索引默认是按照升序进行扫描，没有hint的时候，每次都要从索引的开头进行对比，直到匹配到，随着ip变量值的增大，consistent gets也会变大。但是降序检索直接就可以定位到所需索引块。

 这个案例其实没有必要创建start_ip和end_ip两字段的联合索引，只需要创建一个字段上的索引即可。

create  index idx_lin on country_ip_range (end_ip1);

或者
create  index idx_lin on country_ip_range (start_ip1 desc); --这里ddl如果不用desc关键字，则需要使用 INDEX_RS_DESC 的hint 才能与前一个索引达到相同的效果。

明白了这个case，相信你对索引的理解又会加深一步！