表A(数据量为5000)
ip_no decimal(15,0)
country varchar2(50)
province varchar2(50)
city varchar2(50)表B(数据量为30万)
begin_ip_no decimal(15,0)
end_ip_no decimal(15,0)
country varchar2(50)
province varchar2(50)
city varchar2(50)想通过表B的区域信息来更新表A的相关字段,更新SQL如下
UPDATE 表A a, 表B b
SET a.country = b.country, a.province = b.province, a.city = b.city
WHERE a.ip_no >= b.begin_ip_no AND a.ip_no <= b.end_ip_no有没有什么优化的方法?两个表应该建什么样的索引效率会比较高?
ip_no decimal(15,0)
country varchar2(50)
province varchar2(50)
city varchar2(50)表B(数据量为30万)
begin_ip_no decimal(15,0)
end_ip_no decimal(15,0)
country varchar2(50)
province varchar2(50)
city varchar2(50)想通过表B的区域信息来更新表A的相关字段,更新SQL如下
UPDATE 表A a, 表B b
SET a.country = b.country, a.province = b.province, a.city = b.city
WHERE a.ip_no >= b.begin_ip_no AND a.ip_no <= b.end_ip_no有没有什么优化的方法?两个表应该建什么样的索引效率会比较高?
from 表B b
WHERE 表A.ip_no between b.begin_ip_no AND b.end_ip_no
试试
create index up_index on 表B(begin_ip_no,end_ip_no)试下虚拟一张表试下看
update (select a.country countrya,a.province provincea,a.city citya,
b.country countryb,b.province provinceb,b.city cityb from 表A a,表B b
where WHERE a.ip_no >= b.begin_ip_no AND a.ip_no <= b.end_ip_no)
set countrya=countryb,provincea=provinceb,citya=cityb
update (select /×+bypass_ujvc×/a.country countrya,a.province provincea,a.city citya,
b.country countryb,b.province provinceb,b.city cityb from 表A a,表B b
where WHERE a.ip_no >= b.begin_ip_no AND a.ip_no <= b.end_ip_no)
set countrya=countryb,provincea=provinceb,citya=cityb
create index up_index on 表B(begin_ip_no,end_ip_no)试下虚拟一张表试下看
update (select a.country countrya,a.province provincea,a.city citya,
b.country countryb,b.province provinceb,b.city cityb from 表A a,表B b
where WHERE a.ip_no >= b.begin_ip_no AND a.ip_no <= b.end_ip_no)
set countrya=countryb,provincea=provinceb,citya=cityb