我们在项目中遇到了这样的一个问题,要查询ip对应的地址,采用的是google的地图库,
其中我们把地图库导入了数据库,构造了一个这样的表
-- Create table
create table T_GGMAP_IP
(
IPSTART NUMBER not null,
IPEND NUMBER not null,
LOCID NUMBER,
COMPANY VARCHAR2(200)
)
tablespace SKYEYE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_GGMAP_IP
add constraint PK_GGMAP primary key (IPSTART, IPEND)
using index
tablespace SKYEYE
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create unique index IPEND on T_GGMAP_IP (IPEND)
tablespace SKYEYE
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create unique index IPSTART on T_GGMAP_IP (IPSTART)
tablespace SKYEYE
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
IPSTART NUMBER ---是ip段开始的数字表示形式,即为a.b.c.d转化为 a*255*255*255+b*255*255+c*255*255+d*255
IPEND NUMBER not null, ip段的截止数字表示形式
LOCID NUMBER, ip段对应地址的编号
COMPANY VARCHAR2(200)
如果我们要查询某个ip属于那个地址无非是这样
select * from t_GGMAP_IP t where t.ipstart<=query_ip('202.117.0.20') and t.ipend>=query_ip('202.117.0.20')
---query_ip是自定义的一个转化ip为数字的函数(a*255*255*255+b*255*255+c*255*255+d*255
)
查询执行计划发现
SELECT STATEMENT, GOAL = ALL_ROWS 191 10171 233933
TABLE ACCESS BY INDEX ROWID YGUO T_GGMAP_IP 191 10171 233933
INDEX RANGE SCAN YGUO PK_GGMAP 184 330
虽然用到了INDEX RANGE SCAN PK_GGMAP 但实际执行速度非常慢。T_GGMAP_IP表中大概有400W数据
所以改进型的sql不应该使用PK_GGMAP的RANGE SCAN ,最好能直接使用INDEX UNIQUE SCAN
所以sql可以改造
select t2.* from (select max(t.ipstart) ipst from t_GGMAP_IP t where t.ipstart<=query_ip('202.117.0.20')) t1,t_GGMAP_IP t2 where t1.ipst=t2.ipstart and t2.ipend>=query_ip('202.117.0.20')
为
执行计划为
SELECT STATEMENT, GOAL = ALL_ROWS 5 1 36
NESTED LOOPS 5 1 36
VIEW YGUO 3 1 13
SORT AGGREGATE 1 7
FIRST ROW 3 203411 1423877
INDEX RANGE SCAN (MIN/MAX) YGUO IPSTART 3 203411 1423877
TABLE ACCESS BY INDEX ROWID YGUO T_GGMAP_IP 2 1 23
INDEX UNIQUE SCAN YGUO IPSTART 1 1
实际执行起来速度非常好