9.4 コレクションの設計上の考慮点
コレクションを使用した作業時に検討する一定の考慮点があります。
内容は次のとおりです。
9.4.1 ネストを解除する問合せを使用したリレーショナル形式でのオブジェクト・データの表示
コレクションに対してネストを解除する問合せを実行することで、データをフラットな(リレーショナルな)形式で表示できます。
ネストを解除する問合せは、ネストした表およびVARRAYのいずれについても、シングルレベル・コレクションおよびマルチレベル・コレクションで実行できます。
ネストした表は、次の例のように、TABLE構文を使用して問合せに対してネストを解除できます。
例9-4 TABLEファンクションによるネストした表のネスト解除
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_ntab) n ;
ここで、phones_ntabは、ネストした表phones_ntabの属性を指定します。子である行(この例では電話番号)を持たない親である行も確実に取り出されるようにするには、+を使用して外部結合構文を使用します。次に例を示します。
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_ntab) (+) n ;
問合せのSELECT構文のリストが、親表のネストした表の列以外の列を参照しない場合は、ネストした表の記憶表に対してのみ実行されるように、問合せの最適化が実行されます。
ネストを解除する問合せの構文は、VARRAYおよびネストした表のどちらの場合も同じです。たとえば、ネストした表phones_ntabではなく、phones_varという名前のVARRAYの場合を考えてみます。次の例は、TABLE構文を使用してVARRAYを問い合せます。
SELECT p.name_obj, v.num
FROM people_reltab p, TABLE(p.phones_var) v;9.4.1.1 問合せのネストを解除するためのプロシージャおよびファンクションの作成
ネストを解除する問合せを実行するためのプロシージャおよびファンクションを作成できます。たとえば、locationがhomeである電話番号のみを戻す、home_phones()というファンクションを作成できます。home_phones()ファンクションを作成するには、次の例のようなコードを入力します。
例9-5 home_phonesファンクションの作成
CREATE OR REPLACE FUNCTION home_phones(allphones IN phone_ntabtyp)
RETURN phone_ntabtyp IS
homephones phone_ntabtyp := phone_ntabtyp();
indx1 number;
indx2 number := 0;
BEGIN
FOR indx1 IN 1..allphones.count LOOP
IF
allphones(indx1).location = 'home'
THEN
homephones.extend; -- extend the local collection
indx2 := indx2 + 1;
homephones(indx2) := allphones(indx1);
END IF;
END LOOP;
RETURN homephones;
END;
/
9.4.1.2 データのネストを解除するためのTABLEファンクションの問合せ
作成したばかりのhome_phones()ファンクションに基づいて、個人とその自宅の電話番号のリストを問い合せることができます。
例9-6 電話番号の問合せ
例9-5を参照
SELECT p.name_obj, n.num
FROM people_reltab p, TABLE(
CAST(home_phones(p.phones_ntab) AS phone_ntabtyp)) n ;
自宅の電話番号がリストされていない人も含めて、人のリストおよびその自宅の電話番号を問い合せるには、次のように入力します。
SELECT p.name_obj, n.num
FROM people_reltab p,
TABLE(CAST(home_phones(p.phones_ntab) AS phone_ntabtyp))(+) n ;関連項目:
TABLEファンクションの詳細は、『Oracle Database SQL言語リファレンス』および『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください
9.4.2 VARRAYの記憶域上の考慮点
格納されるVARRAYのサイズは、VARRAY内の現行の要素数にのみ依存し、保持できる最大要素数には関係しません。
VARRAYの記憶域には、NULL情報など、わずかなオーバーヘッドがかかるため、格納されるVARRAYのサイズは、(要素のサイズ)×(要素数)より少し大きくなります。
VARRAYは、列値またはLOBとして格納されます。VARRAYの定義時に、宣言されたVARRAYのLIMIT(要素の上限値)を使用して計算されるVARRAYの最大許容サイズに基づいて、VARRAYの格納方法が決定されます。サイズが約4000バイトを超える場合、VARRAYはLOBに格納されます。その他の場合は、その列自体に列値として格納されます。さらに、インラインLOBがサポートされており、(LOBロケータ用に何バイトか予約された)大きなVARRAYの最初の4000バイトに相当する要素は、その行自体の列に格納されます。『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』も参照してください。
9.4.2.1 VARRAYのサイズ変更の伝播について
VARRAY型のサイズを変更する際、依存型については新しい型バージョンが生成されます。
VARRAY列がLOBとして明示的に格納されておらず、元の最大サイズが4000バイト未満の場合、このことに注意する必要があります。増加後のサイズが4000バイト以上になる場合、VARRAY列をLOBとして格納する必要があります。この場合、LOBセグメントおよびLOB索引を含めた必須LOBメタデータ情報を設定する必要があるため、VARRAY列のメタデータをアップグレードするという追加操作が必要になります。
ALTER TYPE文のCASCADEオプションを使用すると、VARRAYのサイズ変更が、依存型および依存表に伝播されます。有効な各依存型に対して新しいバージョンが生成され、前述の様々な異なる場面に基づいて、依存表メタデータが更新されます。VARRAY列がクラスタ表内に存在する場合、クラスタ表はLOBをサポートしていないため、CASCADEオプションを指定したALTER TYPE文は失敗します。
ALTER TYPE文のCASCADEオプションでは、[NOT] INCLUDING TABLE DATAオプションも用意されています。NOT INCLUDING TABLE DATAオプションを使用すると、表のメタデータは更新されますが、データ・イメージは変換されません。VARRAYのイメージを最新バージョンの形式に変換するには、ALTER TYPE CASCADE文でINCLUDING TABLE DATAを明示的に指定するか、またはALTER TABLE UPGRADE文を発行します。
9.4.3 VARRAYおよびネストした表のパフォーマンス
アプリケーションでコレクション全体が1つの単位として操作される場合、VARRAYのパフォーマンスはネストした表よりずっと良好です。VARRAYはまとまって格納され、ネストした表とは異なり、データを取り出すための結合が不要です。
VARRAYの問合せ
ネストを解除する構文を、ネストした表へのアクセスの場合と同様の方法で、VARRAY列へのアクセスにも使用できます。詳細は、「ネストを解除する問合せを使用したリレーショナル形式でのオブジェクト・データの表示」を参照してください。
VARRAYの更新
VARRAYの要素単位の更新は、サポートされていません。このため、VARRAYが更新される場合、古いコレクション全体が新しいコレクションで置き換えられます。
9.4.4 ネストした表の設計上の考慮点
9.4.4.1 ネストした表の記憶域
9.4.4.2 ネストした表の索引
ヒープ表に格納されるネストした表を作成する場合、Oracleデータベースでは、記憶表のNESTED_TABLE_ID列および親表の対応するID列に索引が自動的に作成されます。
NESTED_TABLE_ID列に索引を作成すると、親表とネストした表の結合の実行には必ずNESTED_TABLE_ID列が使用されるため、ネストした表の子である行にさらに効率的にアクセスできるようになります。
9.4.4.3 ネストした表のロケータ
大規模な子集団の場合は、必要に応じて子である行にアクセスできるように、親である行、および子集団に対するロケータを戻せます。子集団のフィルタもできます。ネストした表のロケータを使用すると、各親に対して子である行が不必要に転送されることを回避できます。
次のいずれかのアクションを実行して、ネストした表のロケータを使用して子である行にアクセスできます。
-
OCIコレクション関数をコールします。このアクションは、
OCIColl*関数などのクライアント側コードでコレクション要素にアクセスするときに暗黙的に発生します。最初のアクセスで、コレクション全体が暗黙的に取り出されます。関連項目:
OCIコレクション関数の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
-
SQLを使用して、ネストした表に対応する行を取り出します。
マルチレベル・コレクションでは、任意のネスト・レベルで指定されたコレクションを持つロケータを使用できます。
次の内容では、コレクションをロケータとして取り出すことができる方法を指定します。
9.4.4.3.1 表作成時に指定する方法
コレクション型が列型として使用されていて、NESTED TABLE記憶域句が使用されている場合は、RETURN AS LOCATOR句を使用して、特定のコレクションをロケータとして取り出すように指定できます。
たとえば、inner_tableが3つのレベルのネストした表で構成されたコレクション型だとします。次の例では、RETURN AS LOCATOR句によって、常に第3レベルのネストした表がロケータとして取り出されることが指定されています。
例9-7 RETURN AS LOCATOR句の使用
CREATE TYPE inner_table AS TABLE OF NUMBER;/
CREATE TYPE middle_table AS TABLE OF inner_table;/
CREATE TYPE outer_table AS TABLE OF middle_table;/
CREATE TABLE tab1 (
col1 NUMBER,
col2 outer_table)
NESTED TABLE col2 STORE AS col2_ntab
(NESTED TABLE COLUMN_VALUE STORE AS cval1_ntab
(NESTED TABLE COLUMN_VALUE STORE AS cval2_ntab RETURN AS LOCATOR) );9.4.4.4 セット・メンバーシップ問合せの最適化
ネストした表にある特定の項目を検索する場合、セット・メンバーシップ問合せが役立ちます。
次の問合せでは、子集団のメンバーシップ、具体的には、homeというロケーションがネストした表phones_ntab (親表people_reltabにあります)にあるかどうかをテストします。
SELECT * FROM people_reltab p WHERE 'home' IN (SELECT location FROM TABLE(p.phones_ntab)) ;
Oracleでは、子集団のメンバーシップを内部的にセミ結合に変換することによって、そのメンバーシップをテストする問合せをより効率的に実行しています。ただし、この最適化は、ALWAYS_SEMI_JOIN初期化パラメータが設定されている場合にのみ実行されます。セミ結合を実行する場合、このパラメータの有効な値はMERGEおよびHASHです。これらのパラメータ値によって使用する結合メソッドが示されます。
注意:
前述の例では、homeおよびlocationは子集団要素です。子集団要素がオブジェクト型である場合は、セット・メンバーシップ問合せを実行するマップ・メソッドまたはオーダー・メソッドが必要です。
9.4.5 マルチレベル・コレクションの設計上の考慮点
コレクション型をネストして、真のマルチレベル・コレクションを作成できます。
「コレクション・データ型のサポート」では、ネストした表のネストした表、VARRAYのネストした表、ネストした表のVARRAY、コレクション型の属性を持つオブジェクト型のVARRAYまたはネストした表など、コレクション型をネストする方法について説明しています。これらにより、真のマルチレベル・コレクションが作成されます。
REFを使用して間接的にコレクションをネストすることもできます。たとえば、ネストした表属性またはVARRAY属性を持つオブジェクトを参照する属性を持つオブジェクト型のネストした表を作成できます。実際にはマルチレベル・コレクションのすべての要素にアクセスする必要はない場合は、REFを使用してコレクションをネストすると、ロードする必要があるのは要素そのものではなく、REFのみであるため、パフォーマンスが向上する可能性があります。
真のマルチレベル・コレクション(具体的にはマルチレベルのネストした表)を使用すると、コレクションの個々の要素にアクセスする問合せのパフォーマンスが向上します。すべての要素にアクセスする必要はない場合、ネストした表ロケータを使用すると、プログラムで規定されたアクセスのパフォーマンスが向上する可能性があります。
一連の例で、この種の設計を示します。
内容は次のとおりです。
9.4.5.1 マルチレベル・コレクションを含むオブジェクト表の作成
マルチレベル・コレクションを含むオブジェクト表を作成できます。
REFを使用して別のコレクションをネストするコレクションの例を作成するため、用意したname_objtyp、address_objtypおよびphone_ntabtypの各オブジェクト型を使用して、person_objtypと呼ばれる新しいオブジェクト型を作成します。1人が複数の電話番号を持つことがあるため、phone_ntabtypオブジェクト型がネストした表になっていることに注意してください。
person_objtypオブジェクト型と、people_objtabオブジェクト型のpeople_objtabというオブジェクト表を作成するには、次のSQL文を発行します。
例9-8 マルチレベル・コレクションを含むオブジェクト表の作成
CREATE TYPE person_objtyp AS OBJECT ( id NUMBER(4), name_obj name_objtyp, address_obj address_objtyp, phones_ntab phone_ntabtyp); /
CREATE TABLE people_objtab OF person_objtyp (id PRIMARY KEY)
NESTED TABLE phones_ntab STORE AS phones_store_ntab ;
people_objtab表は、people_reltab表と同じ属性を持ちます。違いは、people_objtabは行オブジェクトを持つオブジェクト表であり、「リレーショナル表の列オブジェクトの記憶域」で示すように、people_reltab表は列オブジェクトを持つリレーショナル表であるという点です。
ここで、people_objtabオブジェクト表にある行オブジェクトを他の表から参照できます。たとえば、次の項目が含まれるprojects_objtab表を作成するとします。
-
各プロジェクトのプロジェクト識別番号
-
各プロジェクトのタイトル
-
各プロジェクトのプロジェクト・リード
-
各プロジェクトの説明
-
各プロジェクトに割り当てられたチームのメンバーを格納するネストした表コレクション
プロジェクト・リードに対してはpeople_objtabにREFを使用でき、チームに対してはREFのネストした表コレクションを使用できます。まず、person_objtypオブジェクト型に基づいて、personref_ntabtypというネストした表オブジェクト型を作成します。
CREATE TYPE personref_ntabtyp AS TABLE OF REF person_objtyp; /
これで、「REFを使用したオブジェクト表の作成」に示すように、オブジェクト表を作成する準備ができました。
9.4.5.2 REFを使用したオブジェクト表の作成
REFを使用してオブジェクト表を作成できます
「マルチレベル・コレクションを含むオブジェクト表の作成」で個人オブジェクト表を作成すると、プロジェクト・オブジェクト表projects_objtabを作成する準備ができました。まずオブジェクト型projects_objtypを作成してから、projects_objtypに基づいてオブジェクト表projects_objtabを作成します。
例9-9 REFを使用したオブジェクト表の作成
CREATE TYPE projects_objtyp AS OBJECT ( id NUMBER(4), title VARCHAR2(15), projlead_ref REF person_objtyp, description CLOB, team_ntab personref_ntabtyp); / CREATE TABLE projects_objtab OF projects_objtyp (id PRIMARY KEY) NESTED TABLE team_ntab STORE AS team_store_ntab ;
people_objtabオブジェクト表およびprojects_objtabオブジェクト表が作成されると、間接的にネストしたコレクションを持つことになります。つまり、projects_objtab表には、people_objtab表にある人物を指すREFのネストした表コレクションが含まれ、people_objtab表にある人物には電話番号のネストした表コレクションがあります。
これで、「オブジェクト表への値の挿入」に示すように値を挿入する準備ができました。
9.4.5.3 PEOPLE_OBJTABオブジェクト表への値の挿入
オブジェクト表を作成すると、その表に値を挿入できます。
この例のように、people_objtab表に値を挿入できます。
例9-10 people_objtabオブジェクト表への値の挿入
INSERT INTO people_objtab VALUES (
0001,
name_objtyp('JOHN', 'JACOB', 'SCHMIDT'),
address_objtyp('1252 Maple Road', 'Fairfax', 'VA', '22033'),
phone_ntabtyp(
phone_objtyp('home', '650.555.0141'),
phone_objtyp('work', '510.555.0122'))) ;
INSERT INTO people_objtab VALUES (
0002,
name_objtyp('MARY', 'ELLEN', 'MILLER'),
address_objtyp('33 Spruce Street', 'McKees Rocks', 'PA', '15136'),
phone_ntabtyp(
phone_objtyp('home', '415.555.0143'),
phone_objtyp('work', '650.555.0192'))) ;
INSERT INTO people_objtab VALUES (
0003,
name_objtyp('SARAH', 'MARIE', 'SINGER'),
address_objtyp('525 Pine Avenue', 'San Mateo', 'CA', '94403'),
phone_ntabtyp(
phone_objtyp('home', '510.555.0101'),
phone_objtyp('work', '650.555.0178'),
phone_objtyp('cell', '650.555.0143'))) ;
例9-11 projects_objtabオブジェクト表への値の挿入
さらに、次のように、REF演算子を使用してpeople_objtabオブジェクト表から選択することによって、projects_objtabリレーショナル表に挿入できます。
INSERT INTO projects_objtab VALUES (
1101,
'Demo Product',
(SELECT REF(p) FROM people_objtab p WHERE id = 0001),
'Demo the product, show all the great features.',
personref_ntabtyp(
(SELECT REF(p) FROM people_objtab p WHERE id = 0001),
(SELECT REF(p) FROM people_objtab p WHERE id = 0002),
(SELECT REF(p) FROM people_objtab p WHERE id = 0003))) ;
INSERT INTO projects_objtab VALUES (
1102,
'Create PRODDB',
(SELECT REF(p) FROM people_objtab p WHERE id = 0002),
'Create a database of our products.',
personref_ntabtyp(
(SELECT REF(p) FROM people_objtab p WHERE id = 0002),
(SELECT REF(p) FROM people_objtab p WHERE id = 0003))) ;
注意:
この例では、REFを格納するためにネストした表を使用していますが、REFをVARRAYに格納することもできます。つまり、REFのVARRAYを作成できます。