ネストされたテーブル、またはそれらが呼ばれている子テーブル（
NESTED TABLE ）の使用は、2つの理由から非常に疑わしいものです：タイプとオブジェクトでは、外部キー（
FOREIGN KEY ）を作成できないこと、および追加のテーブルを使用して機能を実装することは非常に簡単です。 メインテーブルを作成した後、ネストされたテーブルの列を変更できなくなるという事実がなければ、ネストされたテーブルには他の利点があります。 もちろん、2番目のタイプを作成して使用することもできますが、これはかなりの量の作業であり、間違いなく、既存のデータに関する多くの懸念事項です。
ただし、ネストされたテーブルを使用して多対多リレーションシップ（多対多リレーションシップ）を実装するというアイデアがありました。アイデアは非常に魅力的です
MANY-TO-MANY RELATIONSHIPつの外部キーを使用する代わりに、特定のレコードのセットを保存します-これは人間の観点からより論理的ですロジック？ しかし、疑問が生じました-ゲームはろうそくの価値がありますか？
例
例を使用すると、提案された構造を簡単に表示できます。また、システムへの負荷が少ない方法を確認するのにも役立ちます。
真空中に球状のバス艦隊があると仮定します。 バスフリートには、複数のバスと複数のドライバーがあります。 複数のドライバーが同じバスに乗ります（数回のシフト）。 この公園の特徴は、曜日によっては、1人のドライバーが複数のバスに乗ることもできることです。
それらの間の関係を作成することのみを目的とした、2つの非常に単純なテーブルを作成しましょう。
CREATE TABLE tab_bus
( b_id NUMBER PRIMARY KEY
, bus_number VARCHAR2(9) NOT NULL
);
CREATE SEQUENCE seq_bus;
CREATE TABLE tab_driver
( d_id NUMBER PRIMARY KEY
, driver_name VARCHAR2(255) NOT NULL
);
CREATE SEQUENCE seq_driver;
* This source code was highlighted with Source Code Highlighter .
まず、最も一般的なアプローチを使用して関係を実装します。関係のテーブルを作成します。
CREATE TABLE bus_driver
( bus_id NUMBER
, driver_id NUMBER
, CONSTRAINT pk_driver_bus PRIMARY KEY (bus_id, driver_id)
, CONSTRAINT fk_bus_id FOREIGN KEY (bus_id) REFERENCES tab_bus (b_id)
, CONSTRAINT fk_driver_id FOREIGN KEY (driver_id) REFERENCES tab_driver (d_id)
);
* This source code was highlighted with Source Code Highlighter .
2つのフィールドの主キー（
PRIMARY KEY ）は、必要なすべての条件が満たされることを保証します。フィールドの内容（個別に）を空にすることはできず、各関係は一意です。 さらに、このアプローチにより、追加のフィールドを取り除くことができ、自動インデックス付けが可能になります。
例の関係を作成、削除、選択するには、テストの便宜上、手順が使用されます。 さらに、プログラミングの観点からは、プログラムの本体でクエリをコンパイルするよりも便利であるため、プロシージャを使用したテストに興味がありました。 1つの手順にコミットがない（
COMMIT; ）
COMMIT;注意してください。 まず、テストするときは手動で行います。次に、テスト中にリレーションシップを追加および削除する手順がループで開始されます。この場合、コミットが干渉するだけです。
CREATE PROCEDURE add_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
INSERT INTO bus_driver VALUES ( p_bus, p_driver );
END ;
CREATE PROCEDURE drop_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
DELETE bus_driver WHERE bus_id = p_bus AND driver_id = p_driver;
END ;
CREATE PROCEDURE select_relation
( p_data OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_data FOR
SELECT b.bus_number, d.driver_name
FROM tab_bus b, tab_driver d, bus_driver r
WHERE (b.b_id = r.bus_id) AND (r.driver_id = d.d_id);
END ;
* This source code was highlighted with Source Code Highlighter .
ここでの選択手順では、
WHERE構文がテーブル結合（
JOIN ）として使用されることに注意してください。 これには2つの理由があります。Oracleは気にせず、
JOINと見なし、SQLクエリクエリを作成する複雑さを比較する方が便利です。
これで、最初の関係構造の作成が完了しました。 次のリレーションシップスキームが実装されました。2つのテーブルと、リレーションシップのリストを保存する追加のテーブルです。
次に、代替のSQLコードがあります。 2番目のオプションは、まったく同じテーブル構造で作成されましたが、スキームは異なります。 リレーションシップ構造は次のようになります。2つのテーブルがあり、そのうちの1つには各レコードにリレーションシップのリストが格納されます。
ネストされたテーブルの列を使用してテーブルをすぐに作成できますが、この場合、リレーションシップは既製の2つのテーブルから構築されるため、別のアプローチが使用されます。テーブルの1つが変更されます。
CREATE OR REPLACE TYPE obj_list AS OBJECT
( r_driver NUMBER
);
CREATE OR REPLACE TYPE nt_list AS TABLE OF obj_list;
ALTER TABLE tab_bus ADD
( bus_drivers nt_list NULL
) NESTED TABLE bus_drivers STORE AS nt_bus_drivers;
* This source code was highlighted with Source Code Highlighter .
既に述べたように、ネストされたテーブルに外部キーを作成することはできないため、独自の自転車を作成することは理にかなっています。これは間違いなく関係の処理速度に悪影響を及ぼします。
CREATE FUNCTION check_fk
( p_id NUMBER
) RETURN NUMBER IS
fk_count NUMBER;
BEGIN
SELECT COUNT (d_id) INTO fk_count
FROM tab_driver WHERE d_id = p_id;
RETURN fk_count;
END ;
* This source code was highlighted with Source Code Highlighter .
最後に、関係の手順を作成、削除、および選択します。 プロシージャの最後にある例外ブロックに注目する価値があります。 実際には、レコードの最初の値は
UPDATEを使用して追加する必要があります。 実際に記録セル全体を変更します。 ただし、後続の関係は
INSERTを使用して追加されます。 この手順では、すでにレコードが存在することを想定しており、例外が発生した場合、レコードに接続がないかのように動作します。 関係を挿入しようとする前に確認することは意味がありません。これは多くのアクションだからです。 他の例外を処理し、一般的なケースで別のコードを記述する必要がある場合は、例外の処理を明確にし、変数に例外番号を追加して、それに対してアクションを実行する必要があります。
CREATE PROCEDURE add_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
IF (check_fk(p_driver) = 1) THEN
INSERT INTO TABLE
( SELECT bus_drivers
FROM tab_bus
WHERE b_id = p_bus
)
VALUES ( obj_list(p_driver) );
ELSE
RAISE_APPLICATION_ERROR(-20665, 'Record doesn' 't exist.' );
END IF ;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
EXCEPTION
WHEN OTHERS THEN
UPDATE tab_bus
SET bus_drivers = nt_list ( obj_list(p_driver) )
WHERE b_id = p_bus;
END add_relation;
CREATE PROCEDURE drop_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
IF (check_fk(p_driver) = 1) THEN
DELETE TABLE
( SELECT bus_drivers
FROM tab_bus d
WHERE d.b_id = p_bus
) nt
WHERE nt.r_driver = p_bus;
END IF ;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END drop_relation;
CREATE PROCEDURE select_relation
( p_data OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_data FOR
SELECT b.bus_number, d.driver_name
FROM tab_bus b, tab_driver d, TABLE (b.bus_drivers) r
WHERE (b.b_id = p_bus) AND (d.d_id = p_driver);
END ;
* This source code was highlighted with Source Code Highlighter .
テーブルが空の場合に発生するため、削除手順には例外処理もあります。 例外の場合、探しているレコードはもう存在しないため、削除手順に必要なため、何もする必要はありません。
手順の正しい動作を検証するために、同じテスト値が使用されました。
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Viktor Jeliseev' );
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Stepan Kljavin' );
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Marija Baranka' );
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Arsenij Dubov' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'p666pp' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'LT-3216' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'zox-15' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'x234oo' );
BEGIN
add_relation (1, 3);
add_relation (1, 4);
add_relation (3, 3);
add_relation (3, 2);
add_relation (2, 2);
drop_relation (3, 2);
drop_relation (2, 2);
END ;
DECLARE
g_data SYS_REFCURSOR;
BEGIN
select_relation (1, 4, g_data);
select_relation (3, 3, g_data);
select_relation (2, 2, g_data);
select_relation (1, 1, g_data);
END ;
* This source code was highlighted with Source Code Highlighter .
テスト中
テストは各オプションについて数回実施されました。 最初に、選択、手順、および機能が上記のテストデータでデバッグされました。 次に、各データベース（実際にはもちろんスキーマ）に対してプロシージャが作成され、乱数ジェネレーターを使用してテーブルにデータが入力されました。 ほぼ同じ方法で、所定の数の関係が作成されました。
CREATE OR REPLACE
PROCEDURE random_insert_data
( record_count NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
counter NUMBER;
field_value VARCHAR2(255);
BEGIN
FOR counter IN 1..record_count
LOOP
field_value := DBMS_RANDOM.STRING( 'A' , 9);
INSERT INTO tab_bus (b_id, bus_number) VALUES
( seq_bus.NEXTVAL
, field_value
);
END LOOP;
FOR counter IN 1..record_count
LOOP
field_value := INITCAP(DBMS_RANDOM.STRING( 'L' , 6))|| ' ' ||INITCAP(DBMS_RANDOM.STRING( 'L' , 9));
INSERT INTO tab_driver VALUES
( seq_driver.NEXTVAL
, field_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for standart many-to-many relations
CREATE PROCEDURE random_insert_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
add_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for standart many-to-many relations
CREATE PROCEDURE random_delete_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
drop_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for alternative many-to-many system with nested table
CREATE PROCEDURE random_insert_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
add_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for alternative many-to-many system with nested table
CREATE PROCEDURE random_delete_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
drop_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
* This source code was highlighted with Source Code Highlighter .
テスト手順にもコミットがないことに注意してください。
結果
テストは、
n = 4、1000、100000の量のデータで実行されました。各テーブルには
n個のレコードがあり、
n個の関係が作成されていることがわかります。 テストは10箇所で実施され、そのうち6箇所が下の表に示されています。これらの箇所では、時間差が最も大きく、最も重要なデータが表示されています。
テスト中、最初の選択と後続の選択、およびクエリを使用した選択またはカーソルにデータを挿入するプロシージャを使用した選択が区別されました。 主な測定は、関係と異なるサンプルオプションを作成するために行われました。 乱数ジェネレーターが使用されているため、関係の削除はこのリストに含まれていません。関係を削除しようとすると、ほとんどすべての試行が無効になります。
| ウェイレコーディング | サンプル1 | サンプル2 | サンプル3 | サンプル4 | サンプル5 | の作成 |
| 追加します。 表4 | 00.844 | 00.792 | 00.967 | 01.01 | 02.02 | 00.032 |
| c。 表4 | 00.694 | 00.707 | 00.026 | 00.00 | 00.00 | 00.034 |
| 追加します。 テーブル、1000 | 00.642 | 00.645 | 00.698 | 02.02 | 02.02 | 00.142 |
| c。 テーブル、1000 | 00.687 | 00.695 | 00.344 | 00.00 | 00.00 | 00.721 |
| 追加します。 テーブル、100000 | 00.648 | 00.697 | 01.323 | 00.14 | 00.49 | 14.613 |
| c。 テーブル、100000 | 00.741 | 00.829 | 11/11/7 | 00.00 | 00.00 | 84.630 |
時間は秒単位です。
サンプル1：関連変数を使用して、匿名ブロックからカーソルへの単一の関係をフェッチします。
サンプル2：関連変数を使用する手順で1つの関係をサンプリングします。
サンプル3：匿名ブロックからカーソルへの関係を選択します。
サンプル4：カーソル内のプロシージャによるすべての関係の最初の選択。
サンプル5：カーソル内のプロシージャによるすべての関係の後続のサンプル。
追加：
n個の関係を追加する時間。
私たちが1-2秒について話している場合、時間はまったく重要ではありませんが、実際のデータベースが大きくなるか、サーバーが弱くなることが想定されていることに留意する必要があります。 両方のオプションがテストされたサーバーは非常に強力であるという事実にもマイナスがあります。サイド操作は時間がかかりすぎるため、少量のデータを比較することが困難になります。
したがって、代替の実施形態で相互接続を追加するには、標準のものよりも多くの時間（6倍）がかかります。これは、多くの（500を超える）接続を作成および破棄する必要がある場合に代替オプションが適切ではないことを明確に示しています。 そのようなアクションが少ない場合、人間の観点からすると、外部キーチェックの置換などの不必要なアクションにもかかわらず、違いは単純に知覚できません。
次にサンプルについて。 匿名ブロックからのフェッチには、すべての場合でわずか0.5秒かかります。 おそらく、ほとんどの場合、カーソル自体を操作することになります。 しかし、大量のデータ（たとえば、すべて）をサンプリングする場合、ネストしたテーブルはより効率的になります。ほとんど時間が経過せず、代替が遅い場合の唯一のオプションはすべてのデータの最初の選択です。
最後に、最後の比較：クエリ比較。 個人的には、注目すべき違いは見当たりません。
SELECT b.bus_number、d.driver_name
FROM tab_bus b、tab_driver d、
bus_driver r
WHERE（b.b_id = r.bus_id）
AND（r.driver_id = d.d_id）;
| SELECT b.bus_number、d.driver_name
FROM tab_bus b、tab_driver d、
TABLE（b.bus_drivers）r
WHERE（b.b_id = p_bus）
AND（d.d_id = p_driver）;
|
結論
一般に、ネストしたテーブルを使用して多対多のリレーションシップを作成するというアイデアは、当初考えられていたほど絶望的ではありませんでした。 リレーションシップの作成と削除の操作には1桁長い時間がかかるという事実にもかかわらず、大規模なクエリの選択も高速になり、クエリ構造自体の複雑さはほぼ同じです。 サンプルコードの複雑さにより、両方のアプローチは同様に単純ですが、関係を作成または削除するために、代替アプローチの方がより困難です。
次の場合は、ネストされたテーブルを使用してリレーションシップを実装しないでください。
- 多くの場合、それらを作成または削除する必要があります（時間がかかりすぎます）。
- 一般にテーブルはほとんどないか、データベース構造全体が単純です（関係を追加および削除する手順を記述するのに時間がかかりすぎます）。
- データのロジックは、接続されたオブジェクトの等価性を意味します（リレーションシップの列を作成するテーブルを決定することは不可能です）。
代替方法で関係を実装することを考えるのは理にかなっています。
- 1つのテーブルは多対多ベースで他のいくつかのテーブルと接続されています（データの構造とロジックの理解を簡単にします）。
- 関係の作成と削除は非常にまれであり、1つのテーブルとリンクされたテーブルのレコードの削除と追加はあまり頻繁ではありません（速度の低下はありません）。
- データベースを操作するには、手順、表現、および場合によっては選択のみが使用されます。
- 1つではなく、複数のテーブルに外部キーを実装する必要があります（複数の列を作成する場合とは対照的に、特にフィールド識別子の対応をチェックする機能が必要になるため、より洗練されたソリューションです）。