关于ip区间查询sql的写法

我们在项目中遇到了这样的一个问题，要查询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 

 

 实际执行起来速度非常好

 

 

评论

1 楼 kingtiy 2013-05-28