在2017-07-16的文章 区间检索SQL性能优化方法 中，我使用了创建function的方式，来解决匹配不到区间时的buffer gets过多的性能问题。最近在解决一个客户的多表关联区间检索问题时，又琢磨了一下，发现还是可以直接使用SQL，替代原来的function，SQL还真是博大精深啊。
客户现场类似COUNTRY_IP_RANGE表（保存区间信息）的记录数是5000万，有75万记录要做区间匹配，有一部分数据是匹配不到区间的（随机取20条有10条无法匹配），用普通写法，匹配不到区间时，查一条记录大概要耗时1秒多，这个速度是无法接受的；而优化写法不管是否能够匹配到区间，都只要0.29毫秒（处理完75万记录并将结果插入到表，一共3分35秒）。
普通写法（需要存在end_ip1 + start_ip1 联合索引）：
SELECT COUNTRY_CODE
FROM COUNTRY_IP_RANGE
WHERE end_ip1 >:B1 and start_ip1 <= :B1 and rownum=1;
优化写法，原function的替代SQL代码(需要存在end_ip1 单字段上的索引)：
SELECT
case when start_ip1<= :B1 then COUNTRY_CODE
else 'no_match' end
FROM
(SELECT COUNTRY_CODE, start_ip1,end_ip1
FROM COUNTRY_IP_RANGE
WHERE end_ip1 >= :B1 order by end_ip1
) WHERE ROWNUM = 1;
这个写法要比原来function的性能要好那么一丁点，主要优点是简洁了，不用写function那么麻烦。
注：
变量如果超出COUNTRY_IP_RANGE表end_ip1的最大范围，sql会返回"no rows selected"；最大范围内匹配不到区间，则返回"no_match"（这与普通写法不一样）,匹配到区间就显示对应的COUNTRY_CODE字段值。
两种方法性能对比测试：
如果你要自己动手验证一下，可以在qq群16778072下载COUNTRY_IP_RANGE表的dmp文件:COUNTRY_IP_RANGE.dmp.gz：
该表12万多记录，用exp导出并压缩（压缩后2M大小）。
导入方法：
1、unzip COUNTRY_IP_RANGE.dmp.gz
2、imp xxx/xxx file=COUNTRY_IP_RANGE.dmp full=y
如果能够匹配到区间，普通写法与优化写法消耗的buffer gets是一样的，我们这里主要比较匹配不到区间时的性能情况。
普通写法：
--大的ip， 查不到对应区间：
var B1 number;
exec :B1 := 3716446208;
SELECT COUNTRY_CODE
FROM COUNTRY_IP_RANGE
WHERE end_ip1 >:B1 and start_ip1 <= :B1 and rownum=1;
--2 consistent gets （查大的IP，如果查不到， buffer gets数不多）
--小的ip ，查不到对应区间
var B1 number;
exec :B1 := 16777472 ;
SELECT COUNTRY_CODE
FROM COUNTRY_IP_RANGE
WHERE end_ip1 >= :B1 and start_ip1 <= :B1 and rownum=1;
--408 consistent gets
这个buffer gets数比较大了，如果创建的是end_ip1单字段上的索引（优化写法只需要单字段），buffer gets数更是要暴增很多倍。
新的优化写法：
--大的ip ，查不到对应区间：
var B1 number;
exec :B1 := 3716446208;
select
case when start_ip1<= :B1 then COUNTRY_CODE
else 'no_match' end
from
(SELECT COUNTRY_CODE, start_ip1,end_ip1
FROM COUNTRY_IP_RANGE
WHERE end_ip1 >= :B1 order by end_ip1
) where ROWNUM = 1;
--3 consistent gets
--小的ip ，查不到对应区间
var B1 number;
exec :B1 := 16777472 ;
select
case when start_ip1<= :B1 then
COUNTRY_CODE
else 'no_match' end
from
(SELECT COUNTRY_CODE, start_ip1,end_ip1
FROM COUNTRY_IP_RANGE
WHERE end_ip1 >= :B1 order by end_ip1
) where ROWNUM = 1;
--3 consistent gets
测试结果：
对于优化写法，不论大的IP还是小的IP，匹配不到区间都是3个buffer gets。而对于普通写法，匹配不到区间时，buffer gets会随着查询值(:B1)与最大值（max end_ip1）的远近有很大变化，查询值越小效率越差。
问：
优化写法中，有了end_ip1字段上的索引，而且索引是升序扫描的，为什么还要加order by？
答：
如果索引失效，在没有order by的情况下，全表扫描会随机选一条的end_ip1 >= :B1的记录，造成结果错误。索引失效时sql可以慢，但是不可以出错。
