oracle开发 ip库取数据的执行计划问题

网站访问日志都会存在个基本问题，就是根据访问者ip从ip库中获取ip所在国家（貌似有点绕口），它的使用场景比较广泛，如现在的大部分网站都可以通过来访者的地域推送不同的内容。我们数据仓库中，则要通过这个来分析网站的国家访问情况。

废话不多说，上表

session_temp表是我创建的临时表，从本站某天的真实访问数据中把用户的访问ip全部导过来。本站一天的访问数据在30000左右

ip_temp表是本站购买的Ip库，主要由三个字段构成

startip、endip指的是该ip段范围

countryname指的是该ip段所对应的国家

示例值见下图

  

比如对于1944892794这个IP，我们获取它的国家运行如下：

 这种查询单个ip国家的语句一般是用户访问的时候做判断，再根据他的国家做推算。这语句执行了7秒多，意思是用户来访问时，要至少7秒时间才能把页面展示出来，肯定是不能接受的。

我们数据仓库是对当天全部数据做查找，则完全无法运行出结果。这样的速度显然是不能满足生产环境的要求的。如下：

 8分多钟还没出结果，即使数据仓库中也无法忍受这种速度

 

网站那边说用mongodb可以解决问题，我还没试过，估计也不大好集成其他种类数据库的ETL进来。所以还是要从执行计划入手。先看下这句代码的执行计划

这个执行计划有点难懂。oracle 先对session_temp做了全表扫描排序，因为只有2万多行，所以应该是内存排序，问题不大。然后对根据startip<=ip这个条件，对t2表做了过滤,再全表扫描进行排序，400万条数据排序，估计要用到磁盘了。两张表排完后开始进行merge连接，连接条件是endip>=ip.

这个执行计划看着就有点不靠谱，谓语的两句地位相等，应该都用来做连接条件，结果oracle一句变成了条件过滤语句。endip>=ip这个条件并不是等式连接，如果用merge，意味着从session里取出一条ip数据，需要在ip库里把大于这个ip全部扫描一遍，性能是O(session+ip_temp*session)+O(sessionlog)+O(ip_templog)(oracle的排序算法好像是nlogn,内存排）（难怪能跑出rows30G的数据）， 而merge前的排序本身就是为了减少反复的全表扫描，等于耗了大量精力的排序全部白干。

既然merge也要两边都全表扫描，还不如用nest loop，session表远小于ip库表，所以它是驱动表。根据这个思路我们加上hints

select /*+leading(t1) use_nl(t1 t2)*/ * from session_temp t1,ip_temp t2 where t2.startip<=t1.ip and t2.endip>=t1.ip

查看执行计划

看上去合理了一点，不过各项指标貌似没有明显的改善，原因就不知道了。有时候CBO是有点傻，算出来的指标不一定对（暂时这么解释吧）。

看看效果：

运行时间还是不能满足要求。

仔细想想，nest loop会反复扫描外表，如果上面建个索引，每次都index range scan，显然会快很多。所以在session上创建索引:

执行计划似乎没变，还是对两张表都进行全表扫描。

不过后来发现时我理解错了，谷歌了下hints leading的含义，它表示的是先载入t1表，每次一条数据，再去循环扫描t2表。那不就意味着t2表是驱动表了？跟我印象中对不上啊（哪本书上说leading引导的是驱动表），不管怎样，先改改看

 貌似这个就是我想要的执行计划了，先扫描t2表，400多万条数据全表扫一下还是挺快的，去session表关联，由于是不等式连接，所以是索引范围扫描，索引扫一下也很快。所以这个执行计划时间复杂度是O(ip_temp+ip_temp*session)，session是走索引所以速度非常快， ip_temp*session时间可忽略不计。

再测试下，这回4秒钟就出来了

 可见执行计划多么的重要。

还有些困惑的地方，CBO为啥要搞出这么个执行计划（一个Key过滤一个key连接）？为啥做了隐式转换？做个10053可能可以搞出来。