ネットワーク・アドレスの格納を考える
構成管理データベースを考える時、ネットワーク管理情報を適切に格納することは重要である。Oracle以外のデータベースでは以下のように専用のデータ型や関数を提供している。
PostgreSQLの場合
PostgreSQLでは、IPv4アドレス、IPv6アドレス、MACアドレスを格納するデータ型を提供している。（8.9. ネットワークアドレス型）
MySQLの場合
MySQLではIPv4 ネットワークアドレスのドット区切り表現を文字列から１０進数、あるいはその逆の変換を実行する関数を提供している。（IPv6 ネットワークアドレスの変換関数も用意されている。）
inet_aton()
inet_ntoa()
Oracleでネットワークアドレス変換関数を作ってみる
翻って、Oracleでは悲しいくらいにネットワーク情報の格納手段が乏しい。ネットで検索したら以下の中国語サイトに面白い情報があったので、早速引用し試してみる。
Oracle SQL 模拟MySQL的inet_aton()和inet_ntoa()
inet_aton()
SQL> select
2 to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\1')) * 16777216 +
3 to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\2')) * 65536 +
4 to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\3')) * 256 +
5 to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\4')) as IP_NUMBER
6 from
7 (select '192.168.11.1' as ip from dual);
IP_NUMBER
----------
3232238337
dual表に任意のIPアドレスを指定すると、１０進数に変換してくれるクエリーである。
さらに、OTN掲示板で見つけた「１０進数を２進数に変換する」のスレッドから引用した、n進数に変換するTO_BASE関数（元ネタはAskTomらしい）で２進数に表示し直してみる。
つまり32bitで表現されるIPv4アドレスを32バイトの2進数文字列で表示する。
変換関数TO_BASE定義
CREATE OR REPLACE FUNCTION to_base( p_dec in number, p_base in number )
RETURN VARCHAR2
DETERMINISTIC
IS
l_str VARCHAR2(255) DEFAULT NULL;
l_num NUMBER DEFAULT p_dec;
l_hex VARCHAR2(50) DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
BEGIN
IF ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
END IF;
LOOP
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
EXIT WHEN ( l_num = 0 );
END LOOP;
RETURN l_str;
END to_base;
/
IPアドレスの１０進数表現を２進数に変換
SQL> col "BIN_8bit" for a8
SQL> select 3232238337 as "DEC",to_base(3232238337,2) as "BIN_8bit" from dual;
DEC BIN_8bit （参考：オクテットを１０進数に変換）
---------- --------
3232238337 11000000 192
10101000 168
00001011 11
00000001 1
inet_ntoa()
同様にIPアドレスの１０進数表現をドット区切り表現に変換するクエリーは以下となる。
SQL> select 2 trunc(ip/16777216) ||'.'|| 3 trunc(mod(ip,16777216)/65536) ||'.'|| 4 trunc(mod(ip,65536)/256) ||'.'|| 5 trunc(mod(ip,256)) as IP_ADDRESS 6 from 7 (select 3232238337 as ip from dual); IP_ADDRESS ------------ 192.168.11.1
PL/SQLによるinet_aton関数の実装
CREATE OR REPLACE FUNCTION INET_ATON (
ip_addr IN VARCHAR2)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
RETURN(
to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\1')) * 16777216 +
to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\2')) * 65536 +
to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\3')) * 256 +
to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\4'))
);
END;
/
実行例
SQL> select INET_ATON('192.168.11.1') IP_NUMBER from dual;
IP_NUMBER
----------
3232238337
PL/SQLによるinet_ntoa関数の実装
CREATE OR REPLACE FUNCTION INET_NTOA (
ip_number IN NUMBER)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
RETURN(
trunc(ip_number/16777216) ||'.'||
trunc(mod(ip_number,16777216)/65536) ||'.'||
trunc(mod(ip_number,65536)/256) ||'.'||
trunc(mod(ip_number,256))
);
END;
/
実行例
SQL> select INET_NTOA(3232238337) IP_ADDRESS from dual; IP_ADDRESS ---------------- 192.168.11.1
ネットワーク・アドレス管理表を考える
上で作成した関数を利用して、IPアドレスを管理する表の実装を検討する。
CREATE TABLE addr_tbl (ip_number NUMBER ,ip_binary VARCHAR2(128) GENERATED ALWAYS AS (substr(TO_BASE(ip_number,2),1,32)) VIRTUAL ,ip_addr VARCHAR2(60) GENERATED ALWAYS AS (substr(INET_NTOA(ip_number),1,15)) VIRTUAL); ALTER TABLE ADDR_TBL ADD CONSTRAINT PK_ADDR_TBL PRIMARY KEY (IP_NUMBER);
- IPアドレスはinet_aton関数で１０進数表現した値を「ip_number」列に格納し、この列が主キーとなる。
- さらに２進数表現した「ip_binary」列を仮想列で作成している。
- 仮想列をユーザ定義関数で作成する場合、DETERMINISTICな関数である必要がある。
- 人間に最もなじみの深いドット区切り表現も同様に「ip_addr」列として仮想列で作成する。
- つまり実際にデータが格納されるのは「ip_number」列のみで、「ip_binary」列と「ip_addr」列は仮想列として関数により導出される。
SQL> CREATE TABLE addr_tbl 2 (ip_number NUMBER 3 ,ip_binary VARCHAR2(128) GENERATED ALWAYS AS (substr(TO_BASE(ip_number,2),1,32)) VIRTUAL 4 ,ip_addr VARCHAR2(60) GENERATED ALWAYS AS (substr(INET_NTOA(ip_number),1,15)) VIRTUAL); Table created. SQL> ALTER TABLE ADDR_TBL ADD CONSTRAINT PK_ADDR_TBL PRIMARY KEY (IP_NUMBER); Table altered. SQL> desc ADDR_TBL Name Null? Type ------------- -------- ---------------------------- IP_NUMBER NOT NULL NUMBER IP_BINARY VARCHAR2(128) IP_ADDR VARCHAR2(60)
仮想列の定義内容は以下のように確認する。
２進数表現列は32バイトあれば足りるのだが、仮想列を定義する場合はVARCHAR2型128バイトと大きめに定義する必要がある。（これはいろいろ試行錯誤して確認した。）
同様にドット区切り表現列も15バイトではなく60バイトで定義する必要がある。
SQL> SELECT
2 TABLE_NAME,COLUMN_NAME,DATA_TYPE||'('||DATA_LENGTH||')' "Type"
3 ,VIRTUAL_COLUMN, DATA_DEFAULT
4 FROM USER_TAB_COLS WHERE TABLE_NAME='ADDR_TBL';
TABLE_NAME COLUMN_NAME Type VIR DATA_DEFAULT
------------ ------------ --------------- --- --------------------------------------------------
ADDR_TBL IP_NUMBER NUMBER(22) NO
ADDR_TBL IP_BINARY VARCHAR2(128) YES SUBSTR("TEST"."TO_BASE"("IP_NUMBER",2),1,32)
ADDR_TBL IP_ADDR VARCHAR2(60) YES SUBSTR("TEST"."INET_NTOA"("IP_NUMBER"),1,15)
テストデータの格納
データはIP_NUMBER列のみにInsertするだけでよいことと、INET_ATON関数を使用してドット区切り表現でInsertしていることに注目！
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.1'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.2'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.3'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.4'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.5'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.1'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.2'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.3'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.4'));
1 row created.
SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.5'));
1 row created.
SQL> commit;
Commit complete.
テーブル内容の表示
SQL> col IP_ADDR for a18
SQL> col IP_BINARY for a8
SQL> select IP_NUMBER,IP_ADDR,IP_BINARY from ADDR_TBL;
IP_NUMBER IP_ADDR IP_BINAR
---------- ------------------ --------
3232238337 192.168.11.1 11000000
10101000
00001011
00000001
3232238338 192.168.11.2 11000000
10101000
00001011
00000010
3232238339 192.168.11.3 11000000
10101000
00001011
00000011
3232238340 192.168.11.4 11000000
10101000
00001011
00000100
3232238341 192.168.11.5 11000000
10101000
00001011
00000101
3232240897 192.168.21.1 11000000
10101000
00010101
00000001
3232240898 192.168.21.2 11000000
10101000
00010101
00000010
3232240899 192.168.21.3 11000000
10101000
00010101
00000011
3232240900 192.168.21.4 11000000
10101000
00010101
00000100
3232240901 192.168.21.5 11000000
10101000
00010101
00000101
10 rows selected.
指定されたアドレス範囲のIPアドレスを表示させる
SQL> set lines 140
SQL> set autot on
SQL> select IP_ADDR from ADDR_TBL
2 where IP_NUMBER between INET_ATON('192.168.11.0')
and INET_ATON('192.168.11.255');
IP_ADDR
------------------
192.168.11.1
192.168.11.2
192.168.11.3
192.168.11.4
192.168.11.5
Execution Plan
----------------------------------------------------------
Plan hash value: 1140719244
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 0 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX RANGE SCAN| PK_ADDR_TBL | 1 | 45 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("INET_ATON"('192.168.11.0')<="INET_ATON"('192.168.11.255')) 2 - access("IP_NUMBER">="INET_ATON"('192.168.11.0') AND
"IP_NUMBER"<="INET_ATON"('192.168.11.255'))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
58 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
アドレス範囲条件の指定はドット区切り表現で行っているが、inet_aton()を介して10進数に変換しているのでインデックスレンジ検索となっていることがわかる。
検索条件にドット区切り表現をそのまま使うと…
SQL> select IP_NUMBER,IP_ADDR,IP_BINARY from ADDR_TBL
2 where IP_ADDR = '192.168.11.5';
IP_NUMBER IP_ADDR IP_BINAR
---------- ------------------ --------
3232238341 192.168.11.5 11000000
10101000
00001011
00000101
Execution Plan
----------------------------------------------------------
Plan hash value: 3135829337
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 111 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ADDR_TBL | 1 | 111 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("IP_ADDR"='192.168.11.5')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
38 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
740 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
「ip_addr」列にインデックスは設定されていないので、実行計画はフル・テーブル・スキャンとなる。
「ip_addr」列にインデックスを作成してみる
仮想列のよいところは、データがなくてもインデックスが作成できることである。これはファンクション・インデックスに似ている。
そこで、「ip_addr」列にユニーク・インデックスを作成してみる。
SQL> CREATE UNIQUE INDEX IX_IP_ADDR ON ADDR_TBL (IP_ADDR);
Index created.
SQL> select IP_NUMBER,IP_ADDR,IP_BINARY from ADDR_TBL
2 where IP_ADDR = '192.168.11.5';
IP_NUMBER IP_ADDR IP_BINAR
---------- ------------------ --------
3232238341 192.168.11.5 11000000
10101000
00001011
00000101
Execution Plan
----------------------------------------------------------
Plan hash value: 3058182370
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 111 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ADDR_TBL | 1 | 111 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IX_IP_ADDR | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("IP_ADDR"='192.168.11.5')
Statistics
----------------------------------------------------------
96 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
740 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
今度は、インデックスによる単一行検索となった。