如何优化这个UPDATE语句？

Question

UPDATE tmp_aaa SET country =a.Country, province=a.Province, city=a.City FROM IPMapping a, tmp_aaa b WHERE (b.IPValue>=a.IPStartVal) AND (b.IPValue<=a.IPEndVal)说明：
IPMapping是一个IP地址表，记录了32万多条IP地址的信息，IPStartVal字段和IPEndVal字段分别是IP段的起始整数和结束整数。建有（IPStartVal，IPEndVal）索引。
tmp_aaa是需要查询出Country、province和city的IP地址，有9000多个，IPValue是该IP的整数表示。问题：执行一次要花25~30分钟，看执行计划，Nested Loops/Inner Join 部分占成本的70％，也就是处理 WHERE (b.IPValue>=a.IPStartVal) AND (b.IPValue<=a.IPEndVal) 这个条件花最多时间。请问如何优化呢？或者有没有更高效率的语句？

Answer 1

UPDATE   tmp_aaa   SET   country   =a.Country,   province=a.Province,   city=a.City   FROM   IPMapping   a,   tmp_aaa   b   WHERE   b.IPValue between a.IPStartVal AND a.IPEndVal

Answer 2

32万条记录，执行一次要花25~30分钟 ，看执行计划，Nested   Loops/Inner   Join   部分占成本的70％，也就是处理   WHERE   (b.IPValue> =a.IPStartVal)   AND   (b.IPValue <=a.IPEndVal)   这个条件花最多时间
我个人觉得你是没有做好索引建议对 b.IPValue a.IPStartVal 都建立一个索引语句方面应该是没什么问题的了

Answer 3

建有（IPStartVal，IPEndVal）索引
--------
是分别建的索引还是组合索引？如果是组合索引 Nested Loop时候IPEndVal应该不能对应index scan

Answer 4

直接用查询语句无什么可优化的.
可考虑先对tmp_aaa进行处理.下面提供一优化方法供参考.
1.假设tmp_aaa中的所有记录的ipvalue是连续的.先求出tmp_aaa中ipvalue的最大值与最小值,查询时根据此最大值与最小值对
  表IPMAPPING排除不可能的记录
  UPDATE   tmp_aaa   SET   country   =a.Country,   province=a.Province,   city=a.City   
  FROM   IPMapping   a,   tmp_aaa   b    WHERE   (b.IPValue> =a.IPStartVal)   AND   (b.IPValue <=a.IPEndVal) 
         and a.ipstartval between 求出的ipvalue最小值 and  求出的ipvalue最大值
          and a.ipendval between 求出的ipvalue最小值 and 求出的ipvalue最大值
              
  加上以上两条件应该可加快查询的速度.
2.实际应用中tmp_aaa中ipvalue不可能是连续的,按1的假设,ipvalue是获接近获好,如果ipvalue的最大值与最小值之差比记录数大很多,证明中间有很大的间隔,
  如果值不是均匀分布的,可先将表tmp_aaa分成几组值接近的记录,如ipvalue为1,2,3,10,11,12,20,21,22, 可分出3组的最大值与最小值
  (1,3),(10,12),(20,22),分别以此3组值分3次按上面的方法update记录. 按此思路解决问题,我自己未验证过,LZ可试试.   

Answer 5

给b.IPValue建索引就可以了，11秒，谢谢各位了，^_^。