スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

コレクション型

3~4か月前に、ゴチャゴチャ試してみたネタです。
大した内容でもないけど、このブログへの検索キーワードの半分ぐらいはOracleネタなので、キチンと書こうと思いつつ、技術ネタを纏めるのはちょいと骨が折れるから、書くのが遅くなります。。

DB(テーブル)の物理設計において、いわゆる繰り返し項目が存在する場合、
①正規化を行って、繰り返し項目を別テーブル(子テーブル)とする。
②カンマなどの区切り文字を挟んで、1つのカラムの中に連結して格納する。
という、大まかに言うと2通りの方法があるかなと思います。

それぞれメリット・デメリットがあると思いますが、デメリットとしてはこんな感じでしょうか。

①のデメリット
繰り返し項目が1つだとしても、親テーブルの主キー+繰り返し項目のテーブルをわざわさ作成しないといけない。
また、独立しあった繰り返し項目が複数存在する場合、繰り返し項目の分だけテーブルを作成しないといけない。
結果的に、必要なデータを取得する際にテーブル結合(JOIN)が多発し、パフォーマンスが問題になる可能性がある。(登録や更新においても同様)

②のデメリット
例えば、「1,2,3」とカラムに格納されているとして、「2」を含む(又は含まない)レコードを取得したいと思った場合にSQL(SELECT)一発で必要なレコードを抽出できず、アプリ側で全レコードを取得して判断しないといけない。
また、このカラムに索引(B-Tree)を作成しても、「1,2,3」という値に対する索引となるため、有効な索引とならない。
※これらについては、ファンクション索引を定義することで解決できる場合もあるかもしれません。


①②ともイマイチな面が残ってしまいますが、Oracleにはカラムの中に複数の値を格納するコレクション型というものがあり、以下の2種類がサポートされています。
③VARRAY(配列的な型)
④ネストした表(テーブル的な型)

④について、サクッと検証してみました。

<コレクション型の定義>
CREATE OR REPLACE TYPE TP_CODE AS OBJECT (CODE NUMBER(5));
/
CREATE OR REPLACE TYPE TP_CODE_NT AS TABLE OF TP_CODE;
/

-- コレクション型カラムをWHERE句の比較演算で直接利用したい場合、要素値を返すMAPメソッドを定義する。
CREATE OR REPLACE TYPE TP_CODE AS OBJECT
(CODE NUMBER(5)
,MAP MEMBER FUNCTION MF_CODE RETURN NUMBER);
/
CREATE OR REPLACE TYPE BODY TP_CODE AS
MAP MEMBER FUNCTION MF_CODE RETURN NUMBER IS
  BEGIN
    RETURN CODE;
  END MF_CODE;
END;
/
CREATE OR REPLACE TYPE TP_CODE_NT AS TABLE OF TP_CODE;
/



<テーブルの作成>
※下記のCREATE文では、コレクション型カラムの値(要素)は、索引構成表に登録される。
※要素に対して、マスタテーブル等への外部キー制約は設定できないもよう。
CREATE TABLE TEST
(NAME VARCHAR2(100)
,CODES TP_CODE_NT)
TABLESPACE TEST
NESTED TABLE CODES STORE AS TEST_NT(
  (CONSTRAINT TEST_NT_IOT PRIMARY KEY (NESTED_TABLE_ID, CODE))
  ORGANIZATION INDEX COMPRESS
  TABLESPACE TEST);



<データの登録>
INSERT INTO TEST VALUES
('佐藤',TP_CODE_NT(TP_CODE(1),TP_CODE(2),TP_CODE(3)));

INSERT INTO TEST VALUES
('鈴木',TP_CODE_NT(TP_CODE(2)));

INSERT INTO TEST VALUES
('山本',TP_CODE_NT());

INSERT INTO TEST VALUES
('加藤',NULL);



<データの取得>
SELECT * FROM TEST;
↓↓↓
NAME  CODES(CODE)
----  ----------------------------------------------
佐藤  TP_CODE_NT(TP_CODE(1), TP_CODE(2), TP_CODE(3))
鈴木  TP_CODE_NT(TP_CODE(2))
山本  TP_CODE_NT()
加藤  

※TABLE式を使えば、コレクション型カラムの要素と大元のテーブルのレコードをJOINした結果を取得できる。
SELECT NAME, CODE FROM TEST, TABLE(CODES);
↓↓↓
NAME  CODE
----  -------
佐藤   1
佐藤   2
佐藤   3
鈴木   2

SELECT NAME, CODE FROM TEST, TABLE(CODES)(+);
↓↓↓
NAME  CODE
----  -------
佐藤   1
佐藤   2
佐藤   3
鈴木   2
山本   
加藤   

-- CODEに1を持つNAMEを取得
SELECT NAME FROM TEST
WHERE EXISTS(SELECT * FROM TABLE(CODES) WHERE CODE = 1);
↓↓↓
NAME
----
佐藤
※インデックス「TEST_NT_IOT」のユニーク・スキャンが実行されることを確認

-- CODEに1を持たないNAMEを取得
SELECT NAME FROM TEST
WHERE NOT EXISTS(SELECT * FROM TABLE(CODES) WHERE CODE = 1);
↓↓↓
NAME
----
鈴木
山本
加藤
※インデックス「TEST_NT_IOT」のユニーク・スキャンが実行されることを確認

SELECT NAME FROM TEST WHERE CODES IS EMPTY;
↓↓↓
NAME
----
山本

SELECT NAME FROM TEST WHERE CODES IS NULL;
↓↓↓
NAME
----
加藤

-- 各レコードの要素数
SELECT NAME, CARDINALITY(CODES) FROM TEST;
↓↓↓
NAME  要素数
----  -------
佐藤   3
鈴木   1
山本   0
加藤   

SELECT NAME, NVL(CARDINALITY(CODES), 0) FROM TEST;
↓↓↓
NAME  要素数
----  -------
佐藤   3
鈴木   1
山本   0
加藤   0



<データ(要素)の変更>
※特定レコードのコレクション型カラム全体を更新することは当然可能だが、要素単位の変更も可能
-- 要素の追加
INSERT INTO TABLE(SELECT CODES FROM TEST WHERE NAME = '鈴木') A VALUES
(TP_CODE(4));

-- 要素の更新
UPDATE TABLE(SELECT CODES FROM TEST WHERE NAME = '鈴木') A
SET VALUE(A) = TP_CODE(3)
WHERE A.CODE = 2;

-- 要素の削除
DELETE FROM TABLE(SELECT CODES FROM TEST WHERE NAME = '鈴木') A
WHERE A.CODE = 4;



<データのロード(SQL*Loader)>
・制御ファイル
---------------
LOAD DATA
INFILE 'TEST.tsv'
BADFILE 'TEST.bad'
DISCARDFILE 'TEST.dis'
INSERT
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(NAME
,cnt FILLER CHAR(1) ← 要素数を指定する。
,CODES NESTED TABLE COUNT(cnt) (CODES COLUMN OBJECT (CODE))
)
---------------

・ロード対象のファイル(TEST.tsv)
---------------
佐藤,3,1,2,3
鈴木,1,2
山本,0 ← NULLの要素を挿入できませんでした。。
加藤,0
---------------

ちなみに、ダイレクト・パス・ロードは使えなかったです。
念のためORACLEに問い合わせたところ、KROWNを作成して回答してきました。
sqlldr におけるネストした表に対するダイレクト・パス・ロードについて


以上、個人的な興味に基づいて色々と試してみましたが、結果としてで実装することになりました。。
だって、APサーバには100GB近くのメモリを積んでいて、テーブルにある数千万件の全レコードを余裕で読み込んで処理できちゃうから・・・。

CPUの高速化・メモリの大容量化だけでなく、ストレージ(HDD→SSD・フラッシュストレージ)の高速化、そもそもストレージへのアクセスが不要なインメモリDBやクラウド・サービス等といった近年の技術・サービス動向をみると、DB側で対処する事柄が減って、DB屋(特にRDB)で飯が食えるのも何時ぐらいまでなのかなぁと思ったりもします。
ま、エンジニアとして生き続けたいのなら、技術領域を少しずつでも拡げていくしかないですね。
関連記事
スポンサーサイト

この記事へのコメント

プロフィール

あんま覚えてへんわ


「あんま覚えてへんわ」です。

最新記事
最新コメント
月別アーカイブ
カテゴリ

openclose

カレンダー
05 | 2017/06 | 07
- - - - 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 -
ブログ内検索

アクセス数
アクセスランキング
[ジャンルランキング]
日記
4775位
アクセスランキングを見る>>

[サブジャンルランキング]
会社員・OL
880位
アクセスランキングを見る>>

天気予報
QRコード
QR
RSSリンクの表示
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。