IP区間クエリーsqlの書き方について
私たちはプロジェクトでこのような問題に遭遇しました.ipに対応するアドレスを検索するにはgoogleの地図ライブラリを採用しています.
ここでは地図ライブラリをデータベースにインポートし,このようなテーブルを構築した.
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 VARCHR 2(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)クエリー実行計画発見です.
INDEX RANGE SCAN PKを使いましたがGGMAPですが、実際の実行速度は非常に遅いです.T_GGMAP_IPテーブルには400 W程度のデータがあります
だから改良型のsqlはPKを使うべきではありませんGGMAPのRANGE SCANは、INDEX UNIQUE SCANをそのまま使えるのが望ましい
だからsqlは改造できます
を選択します.
実行計画は次のとおりです.
実際の実行速度は非常によい
ここでは地図ライブラリをデータベースにインポートし,このようなテーブルを構築した.
-- 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 VARCHR 2(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テーブルには400 W程度のデータがあります
だから改良型の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
実際の実行速度は非常によい