「IPアドレス」タグアーカイブ

IPアドレスの管理方法を考える②

はじめに

以前投稿した「IPアドレスの管理方法を考える①」では、IPアドレスを管理する専用のファンクションを作成して、実験的なIPアドレス管理方法を提案してみた。

今回はより実践的なIPアドレス情報の管理方法を考えてみよう。

ネットワークとサーバ（ノード）の関係

コンピュータ・ネットワークは２つ以上のコンピュータ（サーバ）を結び相互に通信することを可能にするので、１つのネットワークには複数のサーバ（ノード）が存在する。

一方、１台のサーバはクライアントにサービスを提供するためのサービス・セグメントだけでなく、運用や監視のために運用管理セグメントに接続されていることが一般的だ。つまり１台のサーバにも複数のネットワークが存在している。（複数のネットワークに「足を出す」）

すなわち、ネットワークとサーバには多対多の関係が成り立つのでリレーショナル・データベースではこれらをそのまま管理することはできない。

このため、ネットワークとサーバの間に別のエンティティを持ってきて、２つの一対多関係を組み合わせるモデルとする必要がある。

この中間エンティティは文字通りの「ネットワーク・インターフェース」となる。具体的にはサーバに挿さった複数のNIC（ネットワーク・インターフェース・カード）上のポートとなる。

そしてこれらのポートを識別するのがIPアドレスとなる。

１枚のNICに仮想IP（VIP）として複数のIPアドレスを割り当てることもできるが、このようなモデルを導入すれば仮想IPも管理することができる。

IPアドレス、ネットワークアドレス、サブネットマスクの関係（おさらい）

TCP/IPは１つのIPアドレスでネットワークとノードをまとめて表すことができるのが特徴である。

IPv4 32bitアドレスは、ネットワークを識別する上位のネットワーク部とノードを識別する下位のホスト部に分けられる。
ホスト部のビットが全て0となっているIPアドレスは、ネットワークを識別するアドレスとして特に「ネットワークアドレス」と呼ばれる。

ネットワーク部とホスト部を示すための情報がサブネットマスクで、IPアドレスの32bit値とBIT AND演算（論理積）を施したものがネットワークアドレスとなる。
つまり、IPアドレスが論理的にあるネットワークに属していることを表すことに必要な情報がサブネットマスクであるとも言える。

例：
 IPアドレス　　　　：192.168.  1.1（11000000 10101000 00000001 00000001）
＋
 サブネットマスク　 ：255.255.255.0（11111111 11111111 11111111 00000000）
------------------------------------------------------------------
 ネットワークアドレス ：192.168.  1.0(11000000 10101000 00000001 00000000)

データベースでIPをアドレスを管理するためには、制約等でこれらの関係を考慮する仕組みを考える必要がある。

テーブル等作成（DDL）

上のER図を基に、テーブル等を作成するDDLを紹介する。

DDL

DROP TABLE NETWORK_INTERFACE PURGE;
DROP TABLE NETWORKS PURGE;
DROP TABLE SERVER PURGE;
-- ネットワーク
CREATE TABLE NETWORKS
(
 NETWORK_ADDRESS    NUMBER(12,0) NOT NULL,
 SUBNET_MASK        NUMBER(12,0) NOT NULL,
 DEFAULT_GATEWAY    NUMBER(12,0) NOT NULL,
 BROADCAST_ADDRESS  NUMBER(12,0) NOT NULL,
 NETWORK_NAME       VARCHAR2(30) NOT NULL,
 REMARKS            VARCHAR2(2000)
);
ALTER TABLE NETWORKS
 ADD(CONSTRAINT PK_NETWORK
     PRIMARY KEY (NETWORK_ADDRESS, SUBNET_MASK)
     USING INDEX)
;
-- ネットワークインターフェース
CREATE TABLE NETWORK_INTERFACE
(
 IP_ADDRESS         NUMBER(12,0) NOT NULL,
 NETWORK_ADDRESS    NUMBER(12,0) NOT NULL,
 SUBNET_MASK        NUMBER(12,0) NOT NULL,
 INTERFACE_NAME     VARCHAR2(30) NOT NULL,
 HOST_NAME          VARCHAR2(30),
 SERVER_ID          NUMBER(5,0),
 REMARKS            VARCHAR2(2000)
);
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT PK_NETWORK_INTERFACE
     PRIMARY KEY (IP_ADDRESS)
     USING INDEX)
;
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT CHK_NETWORK
     CHECK (BITAND(IP_ADDRESS,SUBNET_MASK)=NETWORK_ADDRESS))
;
-- サーバ
CREATE TABLE SERVER
(
 SERVER_ID          NUMBER(5,0)  NOT NULL,
 NODE_NAME          VARCHAR2(30) NOT NULL,
 SERVER_GROUP_ID    NUMBER(5,0)  NOT NULL,
 OS_RELEASE_ID      NUMBER(5,0)  NOT NULL,
 REMARKS            VARCHAR2(2000)
);
ALTER TABLE SERVER
 ADD(CONSTRAINT PK_SERVER
     PRIMARY KEY (SERVER_ID) USING INDEX)
;
-- 参照整合性制約
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT FK_NETWORK_INTERFACE
     FOREIGN KEY(NETWORK_ADDRESS, SUBNET_MASK)
     REFERENCES NETWORKS (NETWORK_ADDRESS, SUBNET_MASK))
;
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT FK_SERVER_NETWORK_INTERFACE
     FOREIGN KEY(SERVER_ID)
     REFERENCES SERVER (SERVER_ID))
;

解説

ネットワーク（４行目〜）

原則的にIPアドレス関連は32bit値を10進数表現した形で格納する。「IPアドレスの管理方法を考える①」で紹介したinet_aton関数およびinet_ntoa関数を挿入あるいは参照時に利用する。

ネットワーク・インターフェース（19行目〜）

ネットワーク表の子表であるがIPアドレスでユニークに識別できるのでIPアドレスのみを主キーにしている。（サブネットマスクとネットワークアドレスは外部キー）
（多対多リレーションを解決する関連エンティティは主キーを複合主キーとする（この場合はサーバIDとネットワークアドレス＋サブネットマスク）場合が多いが、サーバ情報はネットワーク情報がある状態で登録する（つまり後で更新する）イメージなので、あえて非依存関係のモデルとしている。）

チェック制約（CHK_NETWORK）で、挿入されるIPアドレスの論理的妥当性（ネットワークアドレスとの整合性）確認を行っている。

サーバ（39行目〜）

サーバのデータ作成に関しては次回とするので今回は割愛。

参照整合性制約（52行目〜）

参照整合性制約（外部キー制約）を作成する際のTipsとしては一番最後に実行するのがよい。

データ作成と確認

環境が整ったところで、実際にデータを作成してみよう。

サンプルとして
「Oracle VM VirtualBox を用いた Oracle Real Application Clusters (RAC) 12c Release 1 環境の構築」
の「2.4 ネットワーク p.9〜」にあるネットワーク情報のデータを作成してみる。

NETWORKS表

DML

INSERT INTO NETWORKS VALUES (
 INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,INET_ATON('192.168.56.1')
,INET_ATON('192.168.56.255')
,'PUBLIC NETWORK'
,'パブリック・ネットワーク'
);
INSERT INTO NETWORKS VALUES (
 INET_ATON('192.168.100.0')
,INET_ATON('255.255.255.0')
,INET_ATON('192.168.100.1')
,INET_ATON('192.168.100.255')
,'PRIVATE NETWORK1'
,'プライベート・ネットワーク1'
);
INSERT INTO NETWORKS VALUES (
 INET_ATON('192.168.200.0')
,INET_ATON('255.255.255.0')
,INET_ATON('192.168.200.1')
,INET_ATON('192.168.200.255')
,'PRIVATE NETWORK2'
,'プライベート・ネットワーク2'
);
COMMIT;

確認

SQL> col DEFAULT_GATEWAY for a16
SQL> col NETWORK_NAME for a17
SQL> SELECT
  2   INET_NTOA(NETWORK_ADDRESS) NETWORK_ADDRESS
  3  ,INET_NTOA(SUBNET_MASK)     SUBNET_MASK
  4  ,INET_NTOA(DEFAULT_GATEWAY) DEFAULT_GATEWAY
  5  ,NETWORK_NAME
  6  FROM
  7   NETWORKS;

NETWORK_ADDRESS  SUBNET_MASK      DEFAULT_GATEWAY  NETWORK_NAME
---------------- ---------------- ---------------- -----------------
192.168.56.0     255.255.255.0    192.168.56.1     PUBLIC NETWORK
192.168.100.0    255.255.255.0    192.168.100.1    PRIVATE NETWORK1
192.168.200.0    255.255.255.0    192.168.200.1    PRIVATE NETWORK2

NETWORK_INTERFACE表

DML

INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.101')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.102')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.201')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0:1');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.202')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0:2');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.203')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0:3');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.254')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.100.101')
,INET_ATON('192.168.100.0')
,INET_ATON('255.255.255.0')
,'eth1');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.100.102')
,INET_ATON('192.168.100.0')
,INET_ATON('255.255.255.0')
,'eth1');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.200.101')
,INET_ATON('192.168.200.0')
,INET_ATON('255.255.255.0')
,'eth2');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.200.102')
,INET_ATON('192.168.200.0')
,INET_ATON('255.255.255.0')
,'eth2');
COMMIT;

確認

SQL> col IP_ADDRESS for a16
SQL> col NETWORK_ADDRESS for a16
SQL> col SUBNET_MASK for a16
SQL> col INTERFACE_NAME for a16
SQL> SELECT
  2   INET_NTOA(IP_ADDRESS)      IP_ADDRESS
  3  ,INET_NTOA(SUBNET_MASK)     SUBNET_MASK
  4  ,INTERFACE_NAME
  5  FROM
  6   NETWORK_INTERFACE;

IP_ADDRESS       SUBNET_MASK      INTERFACE_NAME
---------------- ---------------- ----------------
192.168.56.101   255.255.255.0    eth0
192.168.56.102   255.255.255.0    eth0
192.168.56.201   255.255.255.0    eth0:1
192.168.56.202   255.255.255.0    eth0:2
192.168.56.203   255.255.255.0    eth0:3
192.168.56.254   255.255.255.0    eth0
192.168.100.101  255.255.255.0    eth1
192.168.100.102  255.255.255.0    eth1
192.168.200.101  255.255.255.0    eth2
192.168.200.102  255.255.255.0    eth2

10行が選択されました。

チェック制約の確認

間違ったIPアドレスをエラーとするチェック制約の動作を確認する。

SQL> INSERT INTO NETWORK_INTERFACE (
  2   IP_ADDRESS
  3  ,NETWORK_ADDRESS
  4  ,SUBNET_MASK
  5  ,INTERFACE_NAME)
  6  VALUES (
  7   INET_ATON('192.168.57.101')  <== 第2オクテットをわざと間違えてInsert
  8  ,INET_ATON('192.168.56.0')
  9  ,INET_ATON('255.255.255.0')
 10  ,'eth0');
INSERT INTO NETWORK_INTERFACE (
*
行1でエラーが発生しました。:
ORA-02290: チェック制約(CM.CHK_NETWORK)に違反しました

今回はここまで

IPアドレスの管理方法を考える①

ネットワーク・アドレスの格納を考える

構成管理データベースを考える時、ネットワーク管理情報を適切に格納することは重要である。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

今度は、インデックスによる単一行検索となった。

2024年4月
月	火	水	木	金	土	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30