第2章 テーブルの内容を比較する
この章では、「テーブル内容の比較」という簡単な例を通して、
さまざまなテーブル結合、集合演算について説明します。
また、それらの SQL を使った方法と、PL/SQL の手続き処理的な機能を使った方法とで
「テーブル内容比較」処理を作成し、各方法の、パフォーマンス、実行計画を、比較・分析します。
|
「テーブル内容比較」処理の概要
「テーブル内容比較」処理の例として、得意先からの請求データと、自社の支払予定データとの、
マッチング処理を考えます。
請求データと、支払予定データとを比較して、一致していれば "一致"、一致していなければ "不一致"
という文字列を返す関数を作成することにします。
サンプルとして次のテーブルを使用します。
請求データ
項目名 | 型 |
得意先CD | NUMBER(10,0) |
請求金額 | NUMBER(10,0) |
支払予定データ
項目名 | 型 |
得意先CD | NUMBER(10,0) |
支払金額 | NUMBER(10,0) |
この2つのテーブルの内容が一致していると判断できるのは、次の場合です。
・両方のテーブルとも、0件
・両方のテーブルのレコード件数が同じで、全データの「得意先」と「金額」が同じ
また、内容が一致していないと判断できるのは、次の場合です。
・両方のテーブルのレコード件数が違う
・請求データがあって、支払予定がない得意先が存在する
・支払予定データがあって、請求データがない得意先が存在する
・「得意先」が同じで「金額」の違うデータがある
2.1. テーブルを作成する
先ほど示したテーブル定義に従って、「請求データ」テーブル、「支払予定データ」テーブルを作成します。
|
テーブルを作成する前に、1章で作成したユーザーで接続しておきます。
CONN johnny/cougar
「請求データ」テーブルを作成します。
テーブルの表領域として、"sample_data"、
プライマリーキーの表領域として、"sample_index" を指定します。
(表領域の指定は必須ではありません。
省略時は、ユーザー作成時に "DEFAULT TABLESPACE" で指定した表領域が使用されます。)
CREATE TABLE "T_請求"
(
"得意先CD" NUMBER(10, 0) NOT NULL
, "請求金額" NUMBER(10, 0)
, CONSTRAINT "PK_請求" PRIMARY KEY ("得意先CD") USING INDEX
TABLESPACE sample_index
) TABLESPACE sample_data
;
同様に「支払予定データ」テーブルを作成します。
CREATE TABLE "T_支払予定"
(
"得意先CD" NUMBER(10, 0) NOT NULL
, "支払金額" NUMBER(10, 0)
, CONSTRAINT "PK_支払予定" PRIMARY KEY ("得意先CD") USING INDEX
TABLESPACE sample_index
) TABLESPACE sample_data
;
削除するには、次のようにします。
DROP TABLE "T_請求" CASCADE CONSTRAINTS
;
DROP TABLE "T_支払予定" CASCADE CONSTRAINTS
;
テーブルの作成結果は次のようにして、確認できます。
CONN system/manager
SELECT table_name
, tablespace_name
FROM dba_tables
WHERE owner = 'JOHNNY'
AND table_name IN ('T_請求', 'T_支払予定')
;
SELECT table_name
, column_name
, data_type
, data_length
, data_precision
, data_scale
, nullable
FROM dba_tab_cols
WHERE owner = 'JOHNNY'
AND table_name IN ('T_請求', 'T_支払予定')
;
SELECT index_name
, index_type
, table_name
, uniqueness
, tablespace_name
FROM dba_indexes
WHERE owner = 'JOHNNY'
AND table_name IN ('T_請求', 'T_支払予定')
;
実行結果 | |
TABLE_NAME TABLESPACE_NAME
----------- ---------------
T_支払予定 SAMPLE_DATA
T_請求 SAMPLE_DATA
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE N
----------- ------------ ---------- ----------- -------------- ---------- -
T_支払予定 得意先CD NUMBER 22 10 0 N
T_支払予定 支払金額 NUMBER 22 10 0 Y
T_請求 得意先CD NUMBER 22 10 0 N
T_請求 請求金額 NUMBER 22 10 0 Y
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES TABLESPACE_NAME
----------- ----------- ----------- --------- ---------------
PK_支払予定 NORMAL T_支払予定 UNIQUE SAMPLE_INDEX
PK_請求 NORMAL T_請求 UNIQUE SAMPLE_INDEX
|
補 足 | |
■引用符で囲まれた識別子について
"T_請求"、"得意先CD" を見て、「えっ、文字列?」と思う人もいるようですが、
これらは、「引用符で囲まれた識別子」と言います。
通常の、「識別子」には、A-Z、a-z、0-9、_、$、# しか使えませんが、
「引用符で囲まれた識別子」には、スペースも含めて、印刷可能な全ての文字を使用できます。
|
2.2. テストデータ登録用プログラムを作成する
テーブル比較処理を検証するためのテストデータを登録するプログラムを作成します。
テーブル内容が一致しているケース、一致していないケースについて、
テストを行うたびに INSERT 文を書いてデータを登録するのではなく、
テストケースの目的に沿ったデータを簡単に登録できるようにしておきます。
|
2.2.1. 全データを削除するプログラム
まず最初に、両方のテーブルから全データを削除する処理を作成します。
CREATE OR REPLACE PROCEDURE chapter02_clear_data IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE "T_請求"';
EXECUTE IMMEDIATE 'TRUNCATE TABLE "T_支払予定"';
END chapter02_clear_data;
/
PL/SQL では、"CREATE 〜"、"DROP 〜"、"ALTER 〜"、"TRUNCATE TABLE 〜" などの DDL文は使用できませんので
CREATE OR REPLACE PROCEDURE chapter02_clear_data IS
BEGIN
TRUNCATE TABLE "T_請求";
TRUNCATE TABLE "T_支払予定";
END chapter02_clear_data;
/
のようには、記述できません。
警告: プロシージャが作成されましたが、コンパイル・エラーがあります。
エラー内容を表示するには、"SHOW ERRORS" コマンドを使用します。
SQL> SHOW ERRORS
PROCEDURE CHAPTER02_CLEAR_DATAのエラーです。
LINE/COL ERROR
-------- -----------------------------------------------------------
3/14 PLS-00103: 記号"TABLE"が見つかりました。
次のうちの1つが入るとき:
:= .
( @ % ;
2.2.2. テストデータを登録するプログラム
テストデータを登録するプログラムを作成します。
パラメータとして、
・得意先CD、金額とも同じ「一致データ件数」
・得意先CD が同じで、金額が違う「金額不一致件数」
・請求データがあって、支払予定データがない「請求データだけの件数」
・支払予定データがあって、請求データがない「支払予定データだけの件数」
を指定できるようにしておきます。
また、省略時の規定値を"0"としておきます。
CREATE OR REPLACE PROCEDURE chapter02_make_data
(
"一致件数" IN NUMBER DEFAULT 0
, "金額不一致件数" IN NUMBER DEFAULT 0
, "請求データだけの件数" IN NUMBER DEFAULT 0
, "支払予定データだけの件数" IN NUMBER DEFAULT 0
)
IS
"得意先NO" PLS_INTEGER := 0;
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 得意先CD、金額とも同じデータを登録します。
FOR i IN 1.."一致件数"
LOOP
"得意先NO" := "得意先NO" + 1;
INSERT INTO "T_請求" ("得意先CD", "請求金額") VALUES ("得意先NO", 1000);
INSERT INTO "T_支払予定" ("得意先CD", "支払金額") VALUES ("得意先NO", 1000);
END LOOP;
-- 得意先CD が同じで、金額が違うデータを登録します。
FOR i IN 1.."金額不一致件数"
LOOP
"得意先NO" := "得意先NO" + 1;
INSERT INTO "T_請求" ("得意先CD", "請求金額") VALUES ("得意先NO", 1000);
INSERT INTO "T_支払予定" ("得意先CD", "支払金額") VALUES ("得意先NO", 2000);
END LOOP;
-- 請求データだけを登録します。
FOR i IN 1.."請求データだけの件数"
LOOP
"得意先NO" := "得意先NO" + 1;
INSERT INTO "T_請求" ("得意先CD", "請求金額") VALUES ("得意先NO", 1000);
END LOOP;
-- 支払予定データだけを登録します。
FOR i IN 1.."支払予定データだけの件数"
LOOP
"得意先NO" := "得意先NO" + 1;
INSERT INTO "T_支払予定" ("得意先CD", "支払金額") VALUES ("得意先NO", 1000);
END LOOP;
COMMIT;
END chapter02_make_data;
/
補 足 | |
■初期値の設定について
初期値の設定には、
"得意先NO" PLS_INTEGER := 0;
"得意先NO" PLS_INTEGER DEFAULT 0;
の、どちらの方法を使ってもかまいません。
ただし、単なる初期化には ":=" を、何か意味のある値には "DEFAULT" を使うよう
推奨されています。
line_count PLS_INTEGER := 0; -- 単なる初期化
line_limit PLS_INTEGER DEFAULT 55; -- 意味のある値
|
2.3. テストデータを登録する
「処理概要」で示した、一致・不一致の条件
・両方のテーブルとも、0件
・両方のテーブルのレコード件数が同じで、全データの「得意先」と「金額」が同じ
・両方のテーブルのレコード件数が違う
・請求データがあって、支払予定がない得意先が存在する
・支払予定データがあって、請求データがない得意先が存在する
・「得意先」が同じで「金額」の違うデータがある
について、もう少し細かく場合分けして、テストデータを登録します。
|
2.3.1. ケース1 両テーブルとも、0件
両方のテーブルから全データを削除します。
CREATE OR REPLACE PROCEDURE chapter02_make_data01 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
END chapter02_make_data01;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data01
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
レコードが選択されませんでした。
SQL> SELECT * FROM T_支払予定;
レコードが選択されませんでした。
|
2.3.2. ケース2 両テーブルのレコード件数が同じで、全データの「得意先」と「金額」が同じ
両方のテーブルから全データを削除した後、
両方のテーブルに、「得意先」と「金額」が同じデータを登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data02 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 両方のテーブルに、「得意先」と「金額」が同じデータを 1件登録します。
chapter02_make_data
(
"一致件数" => 1
);
END chapter02_make_data02;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data02
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
|
補 足 | |
■名前表記法について
chapter02_make_data
(
"一致件数" => 1
, "金額不一致件数" => 2
, "請求データだけの件数" => 3
, "支払予定データだけの件数" => 4
);
のような書き方は、見慣れない人もいると思います(このような表記法を、名前表記法と言います)。
通常は、
chapter02_make_data(1, 2, 3, 4);
と書くところです(これを位置表記法と言います)。
これだと、"1" や "2" が、何を表しているのか分かりません。
位置表記法による呼び出しで、それぞれの引数が何かを説明するために
chapter02_make_data
(
1 -- 一致件数
, 2 -- 金額不一致件数
, 3 -- 請求データだけの件数
, 4 -- 支払予定データだけの件数
);
のように、親切にコメントを書いている例も見かけますが、
せっかく便利な機能が備わっているのですから、このようなケースでは名前表記法を使いましょう。
|
2.3.3. ケース3 「支払予定」データだけがあって、「請求」データがない
両方のテーブルから全データを削除した後、
「支払予定」データだけを登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data03 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
--「支払予定」データだけを1件登録します。
chapter02_make_data
(
"支払予定データだけの件数" => 1
);
END chapter02_make_data03;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data03
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
レコードが選択されませんでした。
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
|
2.3.4. ケース4 「請求」データだけがあって、「支払予定」データがない
両方のテーブルから全データを削除した後、
「請求」データだけを登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data04 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
--「請求」データだけを1件登録します。
chapter02_make_data
(
"請求データだけの件数" => 1
);
END chapter02_make_data04;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data04
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
SQL> SELECT * FROM T_支払予定;
レコードが選択されませんでした。
|
2.3.5. ケース5 「請求」データの件数が多い
両方のテーブルから全データを削除、
両方のテーブルに、「得意先」と「金額」が同じデータを登録し、
「請求」データを余分に登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data05 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 両方のテーブルに、「得意先」と「金額」が同じデータを 1件、
--「請求」データを余分に 1件登録します。
chapter02_make_data
(
"一致件数" => 1
, "請求データだけの件数" => 1
);
END chapter02_make_data05;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data05
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
2 1000
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
|
2.3.6. ケース6 「支払予定」データの件数が多い
両方のテーブルから全データを削除、
両方のテーブルに、「得意先」と「金額」が同じデータを登録し、
「支払予定」データを余分に登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data06 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 両方のテーブルに、「得意先」と「金額」が同じデータを 1件、
--「支払予定」データを余分に 1件登録します。
chapter02_make_data
(
"一致件数" => 1
, "支払予定データだけの件数" => 1
);
END chapter02_make_data06;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data06
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
2 1000
|
2.3.7. ケース7 両テーブルのレコード件数は同じだが、得意先が違うデータがある
両方のテーブルから全データを削除、
両方のテーブルに、「得意先」と「金額」が同じデータを登録し、
「得意先CD」の違う「請求」データ、「支払予定」データを登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data07 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 両方のテーブルに、「得意先」と「金額」が同じデータを 1件、
--「請求」データを余分に 1件、
--「支払予定」データを余分に 1件登録します。
chapter02_make_data
(
"一致件数" => 1
, "請求データだけの件数" => 1
, "支払予定データだけの件数" => 1
);
END chapter02_make_data07;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data07
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
2 1000
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
3 1000
|
2.3.8. ケース8 両テーブルのレコード件数は同じで、得意先も同じだが、金額が違うデータがある
両方のテーブルから全データを削除、
両方のテーブルに、「得意先」と「金額」が同じデータを登録し、
「金額」の違う「請求」データ、「支払予定」データを登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data08 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 両方のテーブルに、「得意先」と「金額」が同じデータを 1件、
--「金額」の違うデータを 1件登録します。
chapter02_make_data
(
"一致件数" => 1
, "金額不一致件数" => 1
);
END chapter02_make_data08;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data08
PL/SQLプロシージャが正常に完了しました・
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
2 1000
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
2 2000
|
2.3.9. ケース9 レコード件数は同じだが、得意先が違うデータ、金額が違うデータがある
両方のテーブルから全データを削除、
両方のテーブルに、「得意先」と「金額」が同じデータ、
「金額」の違う「請求」データ、「支払予定」データ、
「得意先CD」の違う「請求」データ、「支払予定」データを登録します。
CREATE OR REPLACE PROCEDURE chapter02_make_data09 IS
BEGIN
-- 両方のテーブルから、全データを削除します。
chapter02_clear_data;
-- 両方のテーブルに、「得意先」と「金額」が同じデータを 1件、
--「金額」の違うデータを 1件、
--「請求」データを余分に 1件、
--「支払予定」データを余分に 1件登録します。
chapter02_make_data
(
"一致件数" => 1
, "金額不一致件数" => 1
, "請求データだけの件数" => 1
, "支払予定データだけの件数" => 1
);
END chapter02_make_data09;
/
実行結果 | |
SQL> EXECUTE chapter02_make_data09
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM T_請求;
得意先CD 請求金額
---------- ----------
1 1000
2 1000
3 1000
SQL> SELECT * FROM T_支払予定;
得意先CD 支払金額
---------- ----------
1 1000
2 2000
4 1000
|
2.4. 検証用プログラムを作成する
テーブル内容が一致していれば '一致'、不一致であれば '不一致' という文字列を返す
関数 "chapter02_compare" があるとして、
その関数の妥当性を検証するプログラムを作成します。
まだ作成していないプログラムのテストを先に作るという事に、最初は戸惑うかも知れませんが、
次のようなメリットがあります。
・テスト用のプログラムを作ることによって、仕様が明確になる、
・テストを意識してプログラムを作るようになるため、テストしやすいプログラムになり、
再利用性の高いプログラムが作成できる
本書では、このテストファーストという考え方を徹底して行きます。
|
「テストデータ作成」→「テーブル内容比較」を、上記9つのケースについて繰り返します。
期待される比較結果と、実際の比較結果とが、全てのテストについて同じであれば「合格」、
1つでも違えば「不合格」と表示します。
「不合格」の場合、全てのケースについての、比較結果を出力します。
また、さまざまな方法で「テーブル内容比較処理」を作成しようとしているので、
どの方法での「テーブル内容比較処理」をテストするのか、引数で渡すことにします。
CREATE OR REPLACE PROCEDURE chapter02_check
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
)
IS
result BOOLEAN := TRUE; -- 全テストの結果 (合格 / 不合格)
expectation VARCHAR2(6); -- 期待される、テーブル内容の比較結果 (一致 / 不一致)
return_value VARCHAR2(6); -- 返ってきた、テーブル内容の比較結果 (一致 / 不一致)
-- 各々のテストの結果
TYPE t_msg IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
tab_msg t_msg;
msg VARCHAR2(255);
BEGIN
FOR i IN 1..9
LOOP
-- 期待される、テーブル内容の比較結果をセットする
IF i = 1 THEN expectation := '一致'; -- 両方のテーブルとも、0件
ELSIF i = 2 THEN expectation := '一致'; -- 件数が同じで、得意先も金額も同じ
ELSIF i = 3 THEN expectation := '不一致'; -- 「請求」データがない
ELSIF i = 4 THEN expectation := '不一致'; -- 「支払予定」データがない
ELSIF i = 5 THEN expectation := '不一致'; -- 「請求」データの件数が多い
ELSIF i = 6 THEN expectation := '不一致'; -- 「支払予定」データの件数が多い
ELSIF i = 7 THEN expectation := '不一致'; -- 得意先が違うデータがある
ELSIF i = 8 THEN expectation := '不一致'; -- 金額が違うデータがある
ELSIF i = 9 THEN expectation := '不一致'; -- 得意先が違うデータ、金額が違うデータがある
END IF;
-- テストデータを作成する
IF i = 1 THEN chapter02_make_data01; -- 両方のテーブルとも、0件
ELSIF i = 2 THEN chapter02_make_data02; -- 件数が同じで、得意先も金額も同じ
ELSIF i = 3 THEN chapter02_make_data03; -- 「請求」データがない
ELSIF i = 4 THEN chapter02_make_data04; -- 「支払予定」データがない
ELSIF i = 5 THEN chapter02_make_data05; -- 「請求」データの件数が多い
ELSIF i = 6 THEN chapter02_make_data06; -- 「支払予定」データの件数が多い
ELSIF i = 7 THEN chapter02_make_data07; -- 得意先が違うデータがある
ELSIF i = 8 THEN chapter02_make_data08; -- 金額が違うデータがある
ELSIF i = 9 THEN chapter02_make_data09; -- 得意先が違うデータ、金額が違うデータがある
END IF;
-- テーブルの内容を比較する
return_value := chapter02_compare(kbn);
-- 返ってきた比較結果を退避する
msg := return_value || ':';
IF i = 1 THEN msg := msg || '両方のテーブルとも、0件';
ELSIF i = 2 THEN msg := msg || '件数が同じで、得意先も金額も同じ';
ELSIF i = 3 THEN msg := msg || '「請求」データがない';
ELSIF i = 4 THEN msg := msg || '「支払予定」データがない';
ELSIF i = 5 THEN msg := msg || '「請求」データの件数が多い';
ELSIF i = 6 THEN msg := msg || '「支払予定」データの件数が多い';
ELSIF i = 7 THEN msg := msg || '得意先が違うデータがある';
ELSIF i = 8 THEN msg := msg || '金額が違うデータがある';
ELSIF i = 9 THEN msg := msg || '得意先が違うデータ、金額が違うデータがある';
END IF;
tab_msg(i) := msg;
-- 返ってきた比較結果が、期待された値と同じでなければ、テスト失敗
IF return_value != expectation THEN
result := FALSE;
END IF;
END LOOP;
IF result THEN
-- 全てのケースについて、テスト成功であれば
DBMS_OUTPUT.PUT_LINE('合格');
ELSE
-- 1つでも失敗であれば
DBMS_OUTPUT.PUT_LINE('不合格');
-- 各々の比較結果を出力する
FOR i IN 1..9
LOOP
DBMS_OUTPUT.PUT_LINE(tab_msg(i));
END LOOP;
END IF;
END chapter02_check;
/
テーブル内容の比較を行う関数 "chapter02_compare" は、まだ作成していないので、コンパイルが通りません。
実行結果 | |
警告: プロシージャが作成されましたが、コンパイル・エラーがあります。
SQL> show errors
PROCEDURE CHAPTER02_CHECKのエラーです。
LINE/COL ERROR
-------- -----------------------------------------------------
32/9 PL/SQL: Statement ignored
32/25 PLS-00201: 識別子CHAPTER02_COMPAREを宣言してください。
|
この「検証用プログラム」の動作確認用に、常に "一致" を返す関数 "chapter02_compare" を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
RETURN '一致';
END chapter02_compare;
/
「検証用プログラム」を再コンパイルします。
ALTER PROCEDURE chapter02_check COMPILE;
"DBMS_OUTPUT" での出力を有効 (SET SERVEROUTPUT ON) にして動作確認します。
SET SERVEROUTPUT ON
EXECUTE chapter02_check(0)
実行結果 | |
SQL> EXECUTE chapter02_check
不合格
一致:両方のテーブルとも、0件
一致:件数が同じで、得意先も金額も同じ
一致:「請求」データがない
一致:「支払予定」データがない
一致:「請求」データの件数が多い
一致:「支払予定」データの件数が多い
一致:得意先が違うデータがある
一致:金額が違うデータがある
一致:得意先が違うデータ、金額が違うデータがある
|
不一致でなければならないケースで、「一致」と判定しているので、「テスト不合格」です。
2.5. 表の結合と集合演算
2つのテーブルを比較して、一致しているか、一致していないかを返してくれるような機能が
あればいいのですが、そんな便利な機能は用意されていませんので、
「テーブル内容比較処理」を作成するには、いくつかの「表の結合」機能や
「集合演算」機能を組み合わせなければいけません。
そこで本題に入る前に、さまざまな「表の結合」と「集合演算」について説明しておきます。
|
サンプルデータとして、
・得意先、金額ともに同じデータ
・金額の違うデータ
・「請求」だけのデータ
・「支払予定」だけのデータ
を、それぞれ1件ずつ登録しておきます。
EXECUTE chapter02_make_data09
SELECT * FROM "T_請求";
得意先CD 請求金額
---------- ----------
1 1000
2 1000
3 1000
SELECT * FROM "T_支払予定";
得意先CD 支払金額
---------- ----------
1 1000
2 2000
4 1000
2.5.1. 直積
複数の表の、全ての組み合わせを返す演算を、「直積」または「無条件結合」といいます。
次の例は、「請求データ」と「支払予定データ」との、全ての組み合わせを返しています。
ご覧のように、全ての組み合わせですから、一見、無意味に思えますが、
さまざまなテーブル結合は、この直積を元に指定された条件で抽出するという考え方です。
SELECT A."得意先CD"
, A."請求金額"
, B."得意先CD"
, B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
1 1000 2 2000
1 1000 4 1000
2 1000 1 1000
2 1000 2 2000
2 1000 4 1000
3 1000 1 1000
3 1000 2 2000
3 1000 4 1000
2.5.2. 等価結合
指定された列同士の値が等しい組み合わせを返す演算を「等価結合」といいます。
等号を使った記述方法、"INNER JOIN" を使った記述方法があります。
次の例は、結合条件に "得意先CD"、"金額" を指定し、
"得意先CD"、"金額" ともに一致する「組み合わせ」を返します。
■ 等号
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
■ INNER JOIN
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
INNER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
次の例は、結合条件に "得意先CD" だけを指定し、
少なくとも "得意先CD" が一致する「組み合わせ」を返します。
■ 等号
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
■ INNER JOIN
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
INNER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
両方のテーブルの項目名が同じものだけを、結合条件に指定する場合は、
"USING" を使って、次のように記述することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
INNER JOIN "T_支払予定" B
USING ("得意先CD")
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
また、"NATURAL" を使って、結合条件を省略することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
NATURAL INNER JOIN "T_支払予定" B
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
2.5.3. 非等価結合
等号以外の比較演算子を使った結合を「非等価結合」といいます。
次の例は、"得意先CD" が同じで "金額" が違うデータを抽出します。
■ !=
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" != B."支払金額"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
2 1000 2 2000
次の例は、"得意先CD" が同じで "請求金額" が "支払金額" より小さいデータを抽出します。
■ <
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" < B."支払金額"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
2 1000 2 2000
2.5.4. 相関サブクエリー
「等価結合」と同じような考え方ですが、"IN" や、"EXISTS" を使って、
指定された列同士の値が等しい「組み合わせ」の「片割れ」だけを返すことができます。
次の例は、結合条件に "得意先CD"、"金額" を指定し、
"得意先CD"、"金額" ともに一致する「支払予定データ」の存在する「請求データ」を返します。
■ IN
SELECT A."得意先CD", A."請求金額"
FROM "T_請求" A
WHERE (A."得意先CD", A."請求金額")
IN (
SELECT B."得意先CD"
, B."支払金額"
FROM "T_支払予定" B
)
;
得意先CD 請求金額
---------- ----------
1 1000
■ EXISTS
SELECT A."得意先CD", A."請求金額"
FROM "T_請求" A
WHERE EXISTS
(
SELECT B."得意先CD", B."支払金額"
FROM "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
)
;
得意先CD 請求金額
---------- ----------
1 1000
次の例のように、結合条件に "得意先CD" だけを指定した場合、
"金額" が一致しているかどうかは判断できません。
■ IN
SELECT A."得意先CD", A."請求金額"
FROM "T_請求" A
WHERE A."得意先CD"
IN (
SELECT B."得意先CD"
FROM "T_支払予定" B
)
;
得意先CD 請求金額
---------- ----------
1 1000
2 1000
■ EXISTS
SELECT A."得意先CD", A."請求金額"
FROM "T_請求" A
WHERE EXISTS
(
SELECT B."得意先CD", B."支払金額"
FROM "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
)
;
得意先CD 請求金額
---------- ----------
1 1000
2 1000
2.5.5. 反結合
先ほどとは逆に、"NOT IN" や、"NOT EXISTS" を使って、
指定された列同士の値が等しくない「組み合わせ」の「片割れ」を返すことができます。
次の例は、結合条件に "得意先CD"、"金額" を指定し、
"得意先CD"、"金額" ともに一致する「支払予定データ」が存在しない「請求データ」を返します。
■ NOT IN
SELECT A."得意先CD", A."請求金額"
FROM "T_請求" A
WHERE (A."得意先CD", A."請求金額")
NOT IN (
SELECT B."得意先CD"
, B."支払金額"
FROM "T_支払予定" B
)
;
得意先CD 請求金額
---------- ----------
2 1000
3 1000
■ NOT EXISTS
SELECT A."得意先CD", A."請求金額"
FROM "T_請求" A
WHERE NOT EXISTS
(
SELECT B."得意先CD", B."支払金額"
FROM "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
)
;
得意先CD 請求金額
---------- ----------
3 1000
2 1000
2.5.6. 外部結合
等価結合は、指定された列同士の値が等しい組み合わせだけを返しますが、
等しい相手が見つからなかった組み合わせも返す演算を「外部結合」と言います。
"LEFT JOIN" "RIGHT JOIN" を使った記述方法、"(+)" を使った記述方法があります。
次の例は、「請求データ」を、"得意先CD"の一致する「支払予定データ」とともに返します。
■ LEFT OUTER JOIN
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
LEFT OUTER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
3 1000
両方のテーブルの項目名が同じものだけを、結合条件に指定する場合は、
"USING" を使って、次のように記述することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
LEFT OUTER JOIN "T_支払予定" B
USING ("得意先CD")
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
3 1000
また、"NATURAL" を使って、結合条件を省略することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
NATURAL LEFT OUTER JOIN "T_支払予定" B
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
3 1000
■ (+)
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"(+)
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
3 1000
次の例は、「支払予定データ」を、"得意先CD"の一致する「請求データ」とともに抽出します。
■ RIGHT OUTER JOIN
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
RIGHT OUTER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
4 1000
両方のテーブルの項目名が同じものだけを、結合条件に指定する場合は、
"USING" を使って、次のように記述することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
RIGHT OUTER JOIN "T_支払予定" B
USING ("得意先CD")
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
4 1000
また、"NATURAL" を使って、結合条件を省略することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
NATURAL RIGHT OUTER JOIN "T_支払予定" B
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
4 1000
■ (+)
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD"(+) = B."得意先CD"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
4 1000
2.5.7. 完全外部結合
どちらか一方が存在する全ての「組み合わせ」を返す演算を「完全外部結合」と言います。
"LEFT JOIN" と "RIGHT JOIN" を合わせた結合です。
次の例は、"得意先CD"の一致する「請求データ」と「支払予定データ」との組み合わせを返します。
■ FULL OUTER JOIN
SELECT A."得意先CD", A."請求金額"
, B."得意先CD", B."支払金額"
FROM "T_請求" A
FULL OUTER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
;
得意先CD 請求金額 得意先CD 支払金額
---------- ---------- ---------- ----------
1 1000 1 1000
2 1000 2 2000
3 1000
4 1000
両方のテーブルの項目名が同じものだけを、結合条件に指定する場合は、
"USING" を使って、次のように記述することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
FULL OUTER JOIN "T_支払予定" B
USING ("得意先CD")
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
3 1000
4 1000
また、"NATURAL" を使って、結合条件を省略することもできます。
SELECT "得意先CD"
, A."請求金額"
, B."支払金額"
FROM "T_請求" A
NATURAL FULL OUTER JOIN "T_支払予定" B
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
3 1000
4 1000
2.5.8. 集合の和
両方のテーブルの全てのレコードを返す演算を「集合の和」といいます。
"UNION" と "UNION ALL" があります。
"UNION ALL" は単に全てのレコードを返しますが、
"UNION" では、重複レコードを除いた結果を返します。
■ UNION
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
UNION
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
1 1000
2 1000
2 2000
3 1000
4 1000
■ UNION ALL
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
1 1000
2 1000
3 1000
1 1000
2 2000
4 1000
2.5.9. 集合の差
片方のテーブルに存在するレコードを除いた結果を返す演算を「集合の差」といいます。
次の例は、一致する「支払予定データ」のない「請求データ」を返します。
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
MINUS
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
2 1000
3 1000
次の例は、一致する「請求データ」のない「支払予定データ」を返します。
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
MINUS
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
;
得意先CD 金額
---------- ----------
2 2000
4 1000
2.5.10. 集合の積
両方のテーブルともに存在するレコードだけを返す演算を「集合の積」といいます。
次の例は、"得意先CD"、"金額" ともに一致する "得意先CD"、"金額" を返します。
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
INTERSECT
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
1 1000
2.6. テーブル内容比較処理を作成する
いよいよ、本題の「テーブル内容比較」処理を作成します。
前節で説明した、さまざまな「表の結合」「集合演算」を組み合わせた方法や
PL/SQL の手続き処理的な機能を使った方法で作成します。
|
集合の考え方でいうと、図 2-6 の灰色部分の要素数が 0 である場合を "一致"、
灰色部分の要素数が 1 以上である場合を "不一致"とします。
図 2-6 | |
|
たとえば、
・得意先、金額ともに同じデータ
・金額の違うデータ
・「請求」だけのデータ
・「支払予定」だけのデータ
を、それぞれ1件ずつ登録してあった場合、
EXECUTE chapter02_make_data09
SELECT * FROM "T_請求";
得意先CD 請求金額
---------- ----------
1 1000
2 1000
3 1000
SELECT * FROM "T_支払予定";
得意先CD 支払金額
---------- ----------
1 1000
2 2000
4 1000
図 2-6 の灰色部分の要素は、次のとおりです。
得意先CD 金額
---------- ----------
2 1000
2 2000
3 1000
4 1000
一回の SQL で、この灰色部分を返してくれるような集合演算はありませんので、
いくつかの集合演算を組み合わせることになります。
2.6.1. "LEFT JOIN" "UNION" "RIGHT JOIN"
まず最初に、図 2-6-1-1 の灰色部分と、図 2-6-1-2 の灰色部分を求め、それらの集合の和を求める、
という考え方で作成してみます。
図 2-6-1-1 | |
|
|
+
|
図 2-6-1-2 | |
|
|
=
|
図 2-6-1-3 | |
|
|
図 2-6-1-1 の灰色部分を求めます。
SELECT A."得意先CD"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"(+)
AND A."請求金額" = B."支払金額"(+)
AND B."得意先CD" IS NULL
AND B."支払金額" IS NULL
;
得意先CD
----------
2
3
次に、図 2-6-1-2 の灰色部分を求めます。
SELECT B."得意先CD"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD"(+) = B."得意先CD"
AND A."請求金額"(+) = B."支払金額"
AND A."得意先CD" IS NULL
AND A."請求金額" IS NULL
;
得意先CD
----------
2
4
それらの集合の和を求めます。
SELECT A."得意先CD"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"(+)
AND A."請求金額" = B."支払金額"(+)
AND B."得意先CD" IS NULL
AND B."支払金額" IS NULL
UNION ALL
SELECT B."得意先CD"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD"(+) = B."得意先CD"
AND A."請求金額"(+) = B."支払金額"
AND A."得意先CD" IS NULL
AND A."請求金額" IS NULL
;
得意先CD
----------
2
3
2
4
この SQL を元に、テーブル内容を比較する関数を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare_1 RETURN VARCHAR2 IS
CURSOR "c_比較" IS
SELECT 'X' FROM DUAL WHERE EXISTS
(
SELECT A."得意先CD"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"(+)
AND A."請求金額" = B."支払金額"(+)
AND B."得意先CD" IS NULL
AND B."支払金額" IS NULL
UNION ALL
SELECT B."得意先CD"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD"(+) = B."得意先CD"
AND A."請求金額"(+) = B."支払金額"
AND A."得意先CD" IS NULL
AND A."請求金額" IS NULL
)
;
"r_比較" "c_比較"%ROWTYPE;
result VARCHAR2(6); -- テーブル内容の比較結果
BEGIN
OPEN "c_比較";
FETCH "c_比較" INTO "r_比較";
-- 1件でもあれば、不一致
IF "c_比較"%FOUND THEN
RESULT := '不一致';
ELSE
RESULT := '一致';
END IF;
CLOSE "c_比較";
--比較結果を返す
RETURN result;
END chapter02_compare_1;
/
「chapter02_compare」から「chapter02_compare_1」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(1)
実行結果 | |
SQL> EXECUTE chapter02_check(1)
合格
|
2.6.2. "MINUS" "UNION" "MINUS"
同じく、図 2-6-1-1 の灰色部分と、図 2-6-1-2 の灰色部分を求め、それらの集合の和を求める、
という考え方ですが、少し別の方法を使ってみます。
まず、図 2-6-1-1 の灰色部分を求めます。
図 2-6-2-1 | |
|
|
-
|
図 2-6-2-2 | |
|
|
=
|
図 2-6-1-1 | |
|
|
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
MINUS
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
2 1000
3 1000
次に、図 2-6-1-2 の灰色部分を求めます。
図 2-6-2-3 | |
|
|
-
|
図 2-6-2-4 | |
|
|
=
|
図 2-6-1-2 | |
|
|
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
MINUS
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
;
得意先CD 金額
---------- ----------
2 2000
4 1000
それらの集合の和を求めます。
図 2-6-1-1 | |
|
|
+
|
図 2-6-1-2 | |
|
|
=
|
図 2-6-1-3 | |
|
|
(
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
MINUS
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
)
UNION ALL
(
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
MINUS
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
)
;
得意先CD 金額
---------- ----------
2 1000
3 1000
2 2000
4 1000
この SQL を元に、テーブル内容を比較する関数を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare_2 RETURN VARCHAR2 IS
CURSOR "c_比較" IS
SELECT 'X' FROM DUAL WHERE EXISTS
(
(
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
MINUS
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
)
UNION ALL
(
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
MINUS
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
)
)
;
"r_比較" "c_比較"%ROWTYPE;
result VARCHAR2(6); -- テーブル内容の比較結果
BEGIN
OPEN "c_比較";
FETCH "c_比較" INTO "r_比較";
-- 1件でもあれば、不一致
IF "c_比較"%FOUND THEN
RESULT := '不一致';
ELSE
RESULT := '一致';
END IF;
CLOSE "c_比較";
--比較結果を返す
RETURN result;
END chapter02_compare_2;
/
「chapter02_compare」から「chapter02_compare_2」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSIF kbn = 2 THEN RETURN chapter02_compare_2;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(2)
実行結果 | |
SQL> EXECUTE chapter02_check(2)
合格
|
2.6.3. "UNION" "MINUS" "INTERSECT"
次に、図 2-6-3-1 の灰色部分から、図 2-6-3-2 の灰色部分を除く、
という考え方で作成してみます。
図 2-6-3-1 | |
|
|
-
|
図 2-6-3-2 | |
|
|
=
|
図 2-6-3-3 | |
|
|
図 2-6-3-1 の灰色部分を求めます。
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
1 1000
2 1000
3 1000
1 1000
2 2000
4 1000
次に、図 2-6-3-2 の灰色部分を求めます。
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
INTERSECT
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
;
得意先CD 金額
---------- ----------
1 1000
図 2-6-3-1 の灰色部分から、図 2-6-3-2 の灰色部分を除きます。
(
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
)
MINUS
(
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
INTERSECT
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
)
;
得意先CD 金額
---------- ----------
2 1000
2 2000
3 1000
4 1000
この SQL を元に、テーブル内容を比較する関数を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare_3 RETURN VARCHAR2 IS
CURSOR "c_比較" IS
SELECT 'X' FROM DUAL WHERE EXISTS
(
(
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
)
MINUS
(
SELECT A."得意先CD"
, A."請求金額" "金額"
FROM "T_請求" A
INTERSECT
SELECT B."得意先CD"
, B."支払金額" "金額"
FROM "T_支払予定" B
)
)
;
"r_比較" "c_比較"%ROWTYPE;
result VARCHAR2(6); -- テーブル内容の比較結果
BEGIN
OPEN "c_比較";
FETCH "c_比較" INTO "r_比較";
-- 1件でもあれば、不一致
IF "c_比較"%FOUND THEN
RESULT := '不一致';
ELSE
RESULT := '一致';
END IF;
CLOSE "c_比較";
--比較結果を返す
RETURN result;
END chapter02_compare_3;
/
「chapter02_compare」から「chapter02_compare_3」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSIF kbn = 2 THEN RETURN chapter02_compare_2;
ELSIF kbn = 3 THEN RETURN chapter02_compare_3;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(3)
実行結果 | |
SQL> EXECUTE chapter02_check(3)
合格
|
2.6.4. FULL OUTER JOIN
同じく、図 2-6-3-1 の灰色部分から、図 2-6-3-2 の灰色部分を除く、
という考え方ですが、WHERE 条件を使う方法です。
図 2-6-3-1 | |
|
|
-
|
図 2-6-3-2 | |
|
|
=
|
図 2-6-3-3 | |
|
|
図 2-6-3-1 の灰色部分を求めます。
SELECT NVL(A."得意先CD", B."得意先CD") "得意先CD"
, NVL(A."請求金額", 0) "請求金額"
, NVL(B."支払金額", 0) "支払金額"
FROM "T_請求" A
FULL OUTER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 0
3 1000 0
4 0 1000
2 0 2000
共通部分を除きます。
SELECT "得意先CD"
FROM
(
SELECT NVL(A."得意先CD", B."得意先CD") "得意先CD"
, NVL(A."請求金額", 0) "請求金額"
, NVL(B."支払金額", 0) "支払金額"
FROM "T_請求" A
FULL OUTER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
)
WHERE "請求金額" != "支払金額"
;
得意先CD
----------
2
3
2
4
この SQL を元に、テーブル内容を比較する関数を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare_4 RETURN VARCHAR2 IS
CURSOR "c_比較" IS
SELECT 'X' FROM DUAL WHERE EXISTS
(
SELECT "得意先CD"
FROM
(
SELECT NVL(A."得意先CD", B."得意先CD") "得意先CD"
, NVL(A."請求金額", 0) "請求金額"
, NVL(B."支払金額", 0) "支払金額"
FROM "T_請求" A
FULL OUTER JOIN "T_支払予定" B
ON A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
)
WHERE "請求金額" != "支払金額"
)
;
"r_比較" "c_比較"%ROWTYPE;
result VARCHAR2(6); -- テーブル内容の比較結果
BEGIN
OPEN "c_比較";
FETCH "c_比較" INTO "r_比較";
-- 1件でもあれば、不一致
IF "c_比較"%FOUND THEN
RESULT := '不一致';
ELSE
RESULT := '一致';
END IF;
CLOSE "c_比較";
--比較結果を返す
RETURN result;
END chapter02_compare_4;
/
「chapter02_compare」から「chapter02_compare_4」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSIF kbn = 2 THEN RETURN chapter02_compare_2;
ELSIF kbn = 3 THEN RETURN chapter02_compare_3;
ELSIF kbn = 4 THEN RETURN chapter02_compare_4;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(4)
実行結果 | |
SQL> EXECUTE chapter02_check(4)
合格
|
2.6.5. "UNION" + グループ化
同じく、図 2-6-3-1 の灰色部分から、図 2-6-3-2 の灰色部分を除く、
という考え方ですが、集計関数を使う方法です。
図 2-6-3-1 | |
|
|
-
|
図 2-6-3-2 | |
|
|
=
|
図 2-6-3-3 | |
|
|
図 2-6-3-1 の灰色部分を求め、集計します。
SELECT "得意先CD"
, SUM("請求金額") "請求金額"
, SUM("支払金額") "支払金額"
FROM
(
SELECT A."得意先CD"
, A."請求金額"
, 0 "支払金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, 0 "請求金額"
, B."支払金額"
FROM "T_支払予定" B
)
GROUP BY "得意先CD"
;
得意先CD 請求金額 支払金額
---------- ---------- ----------
1 1000 1000
2 1000 2000
3 1000 0
4 0 1000
共通部分を除きます。
SELECT "得意先CD"
FROM
(
SELECT "得意先CD"
, SUM("請求金額") "請求金額"
, SUM("支払金額") "支払金額"
FROM
(
SELECT A."得意先CD"
, A."請求金額"
, 0 "支払金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, 0 "請求金額"
, B."支払金額"
FROM "T_支払予定" B
)
GROUP BY "得意先CD"
)
WHERE "請求金額" != "支払金額"
;
得意先CD
----------
2
3
4
この SQL を元に、テーブル内容を比較する関数を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare_5 RETURN VARCHAR2 IS
CURSOR "c_比較" IS
SELECT 'X' FROM DUAL WHERE EXISTS
(
SELECT "得意先CD"
FROM
(
SELECT "得意先CD"
, SUM("請求金額") "請求金額"
, SUM("支払金額") "支払金額"
FROM
(
SELECT A."得意先CD"
, A."請求金額"
, 0 "支払金額"
FROM "T_請求" A
UNION ALL
SELECT B."得意先CD"
, 0 "請求金額"
, B."支払金額"
FROM "T_支払予定" B
)
GROUP BY "得意先CD"
)
WHERE "請求金額" != "支払金額"
)
;
"r_比較" "c_比較"%ROWTYPE;
result VARCHAR2(6); -- テーブル内容の比較結果
BEGIN
OPEN "c_比較";
FETCH "c_比較" INTO "r_比較";
-- 1件でもあれば、不一致
IF "c_比較"%FOUND THEN
RESULT := '不一致';
ELSE
RESULT := '一致';
END IF;
CLOSE "c_比較";
--比較結果を返す
RETURN result;
END chapter02_compare_5;
/
「chapter02_compare」から「chapter02_compare_5」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSIF kbn = 2 THEN RETURN chapter02_compare_2;
ELSIF kbn = 3 THEN RETURN chapter02_compare_3;
ELSIF kbn = 4 THEN RETURN chapter02_compare_4;
ELSIF kbn = 5 THEN RETURN chapter02_compare_5;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(5)
実行結果 | |
SQL> EXECUTE chapter02_check(5)
合格
|
2.6.6. 集合の要素数を数える
「請求」データ、「支払」データ、「共通部分」の件数を数えます。
全てが同じであれば、テーブルの内容が一致していると判断できます。
図 2-6-6-1 | |
|
|
|
図 2-6-6-2 | |
|
|
|
図 2-6-6-3 | |
|
|
「請求」データの件数を数えます。
SELECT COUNT(*)
FROM "T_請求"
;
COUNT(*)
----------
3
「支払」データの件数を数えます。
SELECT COUNT(*)
FROM "T_支払予定"
;
COUNT(*)
----------
3
「共通部分」の件数を数えます。
SELECT COUNT(*)
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
;
COUNT(*)
----------
1
これらの SQL を元に、テーブル内容を比較する関数を作成します。
CREATE OR REPLACE FUNCTION chapter02_compare_6 RETURN VARCHAR2 IS
"一致件数" PLS_INTEGER;
"請求件数" PLS_INTEGER;
"支払件数" PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO "請求件数" FROM "T_請求";
SELECT COUNT(*) INTO "支払件数" FROM "T_支払予定";
-- 両方とも 0 件なら一致
IF ("請求件数" = 0) AND ("支払件数" = 0) THEN
RETURN '一致';
END IF;
-- 件数が違えば 不一致
IF "請求件数" <> "支払件数" THEN
RETURN '不一致';
END IF;
SELECT COUNT(*)
INTO "一致件数"
FROM "T_請求" A
, "T_支払予定" B
WHERE A."得意先CD" = B."得意先CD"
AND A."請求金額" = B."支払金額"
;
-- 請求件数と一致件数が違えば 不一致
IF "請求件数" <> "一致件数" THEN
RETURN '不一致';
END IF;
-- 請求件数、支払件数、一致件数の 全てが一致
RETURN '一致';
END chapter02_compare_6;
/
「chapter02_compare」から「chapter02_compare_6」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSIF kbn = 2 THEN RETURN chapter02_compare_2;
ELSIF kbn = 3 THEN RETURN chapter02_compare_3;
ELSIF kbn = 4 THEN RETURN chapter02_compare_4;
ELSIF kbn = 5 THEN RETURN chapter02_compare_5;
ELSIF kbn = 6 THEN RETURN chapter02_compare_6;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(6)
実行結果 | |
SQL> EXECUTE chapter02_check(6)
合格
|
2.6.7. カーソルを使用して、1件ずつテーブル内容を比較する
リレーショナルデータベースらしくない、昔ながらの方法で作成してみます。
SQL に頼らず、PL/SQL の手続き処理的な機能を使って、1件ずつテーブル内容を比較する方法です。
カーソルを使用して、両方のテーブルを得意先順に1件ずつ読みます。
どちらかのテーブルを読み終わったら、LOOP を抜けます。
片方のテーブルが先に終われば「件数 不一致」と判断できます。
また、得意先CD が等しくなければ「得意先CD 不一致」、
金額 が等しくなければ「金額 不一致」と判断し、LOOP を抜けます。
0001 CREATE OR REPLACE FUNCTION chapter02_compare_7 RETURN VARCHAR2 IS
0002 CURSOR "c_請求" IS
0003 SELECT "得意先CD", "請求金額"
0004 FROM "T_請求"
0005 ORDER BY "得意先CD"
0006 ;
0007 CURSOR "c_支払" IS
0008 SELECT "得意先CD", "支払金額"
0009 FROM "T_支払予定"
0010 ORDER BY "得意先CD"
0011 ;
0012
0013 "r_請求" "T_請求"%ROWTYPE;
0014 "r_支払" "T_支払予定"%ROWTYPE;
0015
0016 result BOOLEAN := TRUE;
0017 BEGIN
0018 OPEN "c_請求";
0019 OPEN "c_支払";
0020
0021 LOOP
0022 FETCH "c_請求" INTO "r_請求";
0023 FETCH "c_支払" INTO "r_支払";
0024
0025 --レコード件数が違う
0026 IF "c_請求"%FOUND != "c_支払"%FOUND THEN
0027 result := FALSE;
0028 END IF;
0029
0030 --どちらかのテーブルに、これ以上レコードがなければ終了
0031 EXIT WHEN "c_請求"%NOTFOUND OR "c_支払"%NOTFOUND;
0032
0033 -- 「得意先」が違う
0034 IF "r_請求"."得意先CD" != "r_支払"."得意先CD" THEN
0035 result := FALSE;
0036 EXIT;
0037 END IF;
0038
0039 -- 各得意先毎の「金額」が違う
0040 IF "r_請求"."請求金額" != "r_支払"."支払金額" THEN
0041 result := FALSE;
0042 EXIT;
0043 END IF;
0044 END LOOP;
0045
0046 CLOSE "c_請求";
0047 CLOSE "c_支払";
0048
0049 --比較結果を返す
0050 IF result THEN
0051 RETURN '一致';
0052 ELSE
0053 RETURN '不一致';
0054 END IF;
0055 END chapter02_compare_7;
0056 /
補 足 | |
0026 IF "c_請求"%FOUND != "c_支払"%FOUND THEN
"c_請求"%FOUND と "c_支払"%FOUND が、一致しないということは、
・"c_請求"にはまだレコードがあるが、"c_支払"にはもうレコードがない
・"c_請求"にはもうレコードがないが、"c_支払"にはまだレコードがある
の、いずれかであるということですから、レコード件数が違うことをあらわしています。
|
「chapter02_compare」から「chapter02_compare_7」を呼び出すよう
変更してテストします。
CREATE OR REPLACE FUNCTION chapter02_compare
(
kbn IN NUMBER -- どの方法での「テーブル内容比較処理」をテストするか
) RETURN VARCHAR2
IS
BEGIN
IF kbn = 1 THEN RETURN chapter02_compare_1;
ELSIF kbn = 2 THEN RETURN chapter02_compare_2;
ELSIF kbn = 3 THEN RETURN chapter02_compare_3;
ELSIF kbn = 4 THEN RETURN chapter02_compare_4;
ELSIF kbn = 5 THEN RETURN chapter02_compare_5;
ELSIF kbn = 6 THEN RETURN chapter02_compare_6;
ELSIF kbn = 7 THEN RETURN chapter02_compare_7;
ELSE RETURN '一致';
END IF;
END chapter02_compare;
/
SET SERVEROUTPUT ON
EXECUTE chapter02_check(7)
実行結果 | |
SQL> EXECUTE chapter02_check(7)
合格
|
2.7. パフォーマンスを計測する
どの方法を使っても結果は同じですが、パフォーマンスについては、どうでしょうか?
次に、各方法のパフォーマンスを計測します。
|
2.7.1. フォーマンス計測用プログラムの作成
まず、パフォーマンスを計測するプログラムを作成します。
CREATE OR REPLACE PROCEDURE chapter02_measure
IS
start_time PLS_INTEGER; -- 開始時間
result VARCHAR2(6); -- 比較結果
msg VARCHAR2(255); -- 結果表示用
BEGIN
FOR i IN 1..7
LOOP
-- 開始時間を取得します
start_time := DBMS_UTILITY.GET_TIME;
-- テーブル内容比較処理を行います
IF i = 1 THEN result := chapter02_compare_1;
ELSIF i = 2 THEN result := chapter02_compare_2;
ELSIF i = 3 THEN result := chapter02_compare_3;
ELSIF i = 4 THEN result := chapter02_compare_4;
ELSIF i = 5 THEN result := chapter02_compare_5;
ELSIF i = 6 THEN result := chapter02_compare_6;
ELSIF i = 7 THEN result := chapter02_compare_7;
END IF;
-- 経過時間を取得します
msg := TO_CHAR((DBMS_UTILITY.GET_TIME - start_time) / 100, '0000.00') || '秒';
-- 結果を表示します
IF i = 1 THEN msg := 'LEFT-JOIN UNION RIGHT-JOIN : ' || msg;
ELSIF i = 2 THEN msg := 'MINUS UNION MINUS : ' || msg;
ELSIF i = 3 THEN msg := 'UNION MINUS INTERSECT : ' || msg;
ELSIF i = 4 THEN msg := 'FULL-OUTER-JOIN : ' || msg;
ELSIF i = 5 THEN msg := 'UNION + グループ化 : ' || msg;
ELSIF i = 6 THEN msg := '集合の要素数を数える : ' || msg;
ELSIF i = 7 THEN msg := 'カーソルを使用して、1件ずつ比較する : ' || msg;
END IF;
DBMS_OUTPUT.PUT_LINE(msg);
END LOOP;
END chapter02_measure;
/
2.7.2. パフォーマンスを計測する
データ件数は、「請求データ」「支払予定データ」ともに、112,000件ずつ。
内訳は、
・一致データ -- 100,000件
・金額不一致 -- 10,000件
・請求だけのデータ -- 1,000件
・支払予定だけのデータ -- 1,000件
として実行してみます。
-- テストデータ登録
BEGIN
chapter02_make_data
(
"一致件数" => 100000
, "金額不一致件数" => 10000
, "請求データだけの件数" => 1000
, "支払予定データだけの件数" => 1000
);
END;
/
--統計情報作成
ANALYZE TABLE "T_請求" ESTIMATE STATISTICS SAMPLE 10 PERCENT
;
ANALYZE TABLE "T_支払予定" ESTIMATE STATISTICS SAMPLE 10 PERCENT
;
-- パフォーマンス計測 (念のため3回おこなう)
SET SERVEROUTPUT ON
BEGIN
chapter02_measure;
chapter02_measure;
chapter02_measure;
END;
/
実行結果 | |
LEFT-JOIN UNION RIGHT-JOIN : 0000.90秒
MINUS UNION MINUS : 0001.51秒
UNION MINUS INTERSECT : 0003.22秒
FULL-OUTER-JOIN : 0000.61秒
UNION + グループ化 : 0002.73秒
集合の要素数を数える : 0001.55秒
カーソルを使用して、1件ずつ比較する : 0009.29秒
LEFT-JOIN UNION RIGHT-JOIN : 0000.59秒
MINUS UNION MINUS : 0001.44秒
UNION MINUS INTERSECT : 0003.03秒
FULL-OUTER-JOIN : 0000.60秒
UNION + グループ化 : 0002.77秒
集合の要素数を数える : 0001.57秒
カーソルを使用して、1件ずつ比較する : 0009.25秒
LEFT-JOIN UNION RIGHT-JOIN : 0000.71秒
MINUS UNION MINUS : 0001.46秒
UNION MINUS INTERSECT : 0003.04秒
FULL-OUTER-JOIN : 0000.61秒
UNION + グループ化 : 0002.77秒
集合の要素数を数える : 0001.57秒
カーソルを使用して、1件ずつ比較する : 0009.14秒
|
「"LEFT JOIN" "UNION" "RIGHT JOIN"」を組み合わせた方法、
「FULL OUTER JOIN」を使った方法が最も高速で、
「カーソルを使用して、1件ずつ比較する」方法に比べて 10倍以上速いことがわかります。
では、「1件ずつ比較する方法」は、一体何にそんなに時間がかかっているのでしょうか?
プロファイリングを行って、確認してみましょう。
2.8.「プロファイリング」を行う
1章で作成したプロファイリング用スクリプトを使用して、「テーブル内容比較処理」の
各々の行で、どれくらい処理時間がかかっているかを見てみます。
|
1章で作成したスクリプトを使用して、プロファイリングを行います。
@c:\plsql\profile.sql
「プロファイリング対象プログラム」の入力を求められますので、
"chapter02_measure" と入力します。
実行結果 | |
SQL> @c:\plsql\profile.sql
program_nameに値を入力してください: chapter02_measure
旧 12: &program_name;
新 12: chapter02_measure;
LEFT-JOIN UNION RIGHT-JOIN : 0000.62秒
MINUS UNION MINUS : 0001.38秒
UNION MINUS INTERSECT : 0002.98秒
FULL-OUTER-JOIN : 0000.61秒
UNION + グループ化 : 0002.78秒
集合の要素数を数える : 0001.65秒
カーソルを使用して、1件ずつ比較する : 0010.75秒
PL/SQLプロシージャが正常に完了しました。
・・・プロファイリング結果 (省略)・・・
|
この後に続いて、プロファイリング結果が出力されますが、
見にくいので、多少編集したものを、以下に掲載します。
■ "LEFT JOIN" "UNION" "RIGHT JOIN"
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_1 1 000418.051633 3 SELECT 'X' FROM DUAL WHERE EXISTS
CHAPTER02_COMPARE_1 1 000000.001276 26 OPEN "c_比較";
CHAPTER02_COMPARE_1 1 000020.116122 28 FETCH "c_比較" INTO "r_比較";
CHAPTER02_COMPARE_1 1 000000.017280 31 IF "c_比較"%FOUND THEN
CHAPTER02_COMPARE_1 1 000000.009429 32 RESULT := '不一致';
CHAPTER02_COMPARE_1 1 000000.050007 37 CLOSE "c_比較";
CHAPTER02_COMPARE_1 2 000000.109024 40 RETURN result;
■ "MINUS" "UNION" "MINUS"
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_2 1 000934.466787 3 SELECT 'X' FROM DUAL WHERE EXISTS
CHAPTER02_COMPARE_2 1 000000.001201 30 OPEN "c_比較";
CHAPTER02_COMPARE_2 1 000000.532755 32 FETCH "c_比較" INTO "r_比較";
CHAPTER02_COMPARE_2 1 000000.004621 35 IF "c_比較"%FOUND THEN
CHAPTER02_COMPARE_2 1 000000.017143 36 RESULT := '不一致';
CHAPTER02_COMPARE_2 1 000000.033559 41 CLOSE "c_比較";
CHAPTER02_COMPARE_2 2 000000.033513 44 RETURN result;
■ "UNION" "MINUS" "INTERSECT"
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_3 1 002044.607426 3 SELECT 'X' FROM DUAL WHERE EXISTS
CHAPTER02_COMPARE_3 1 000000.001344 30 OPEN "c_比較";
CHAPTER02_COMPARE_3 1 000000.500461 32 FETCH "c_比較" INTO "r_比較";
CHAPTER02_COMPARE_3 1 000000.004138 35 IF "c_比較"%FOUND THEN
CHAPTER02_COMPARE_3 1 000000.102006 36 RESULT := '不一致';
CHAPTER02_COMPARE_3 1 000000.031894 41 CLOSE "c_比較";
CHAPTER02_COMPARE_3 2 000000.033469 44 RETURN result;
■ "FULL OUTER JOIN"
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_4 1 000389.537351 3 SELECT 'X' FROM DUAL WHERE EXISTS
CHAPTER02_COMPARE_4 1 000000.001323 23 OPEN "c_比較";
CHAPTER02_COMPARE_4 1 000020.303547 25 FETCH "c_比較" INTO "r_比較";
CHAPTER02_COMPARE_4 1 000000.005948 28 IF "c_比較"%FOUND THEN
CHAPTER02_COMPARE_4 1 000000.020056 29 RESULT := '不一致';
CHAPTER02_COMPARE_4 1 000000.042909 34 CLOSE "c_比較";
CHAPTER02_COMPARE_4 2 000000.115313 37 RETURN result;
■ "UNION" + グループ化
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_5 1 001904.571091 3 SELECT 'X' FROM DUAL WHERE EXISTS
CHAPTER02_COMPARE_5 1 000000.001209 32 OPEN "c_比較";
CHAPTER02_COMPARE_5 1 000000.563212 34 FETCH "c_比較" INTO "r_比較";
CHAPTER02_COMPARE_5 1 000000.004724 37 IF "c_比較"%FOUND THEN
CHAPTER02_COMPARE_5 1 000000.007970 38 RESULT := '不一致';
CHAPTER02_COMPARE_5 1 000000.037144 43 CLOSE "c_比較";
CHAPTER02_COMPARE_5 2 000000.031960 46 RETURN result;
■ 集合の要素数を数える
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_6 1 000023.144004 6 SELECT COUNT(*) INTO "請求件数" FROM "T_請求";
CHAPTER02_COMPARE_6 1 000021.897182 8 SELECT COUNT(*) INTO "支払件数" FROM "T_支払予定";
CHAPTER02_COMPARE_6 1 000000.006590 11 IF ("請求件数" = 0) AND ("支払件数" = 0) THEN
CHAPTER02_COMPARE_6 1 000000.001090 16 IF "請求件数" <> "支払件数" THEN
CHAPTER02_COMPARE_6 1 001055.219877 20 SELECT COUNT(*)
CHAPTER02_COMPARE_6 1 000000.004014 29 IF "請求件数" <> "一致件数" THEN
CHAPTER02_COMPARE_6 1 000000.082805 30 RETURN '不一致';
■ カーソルを使用して、1件ずつ比較する
名前 回数 実行時間 行 ソース
------------------------- --------- -------------- ---- ----------------------------------------------------------
CHAPTER02_COMPARE_7 2 000000.344781 3 SELECT "得意先CD", "請求金額"
CHAPTER02_COMPARE_7 1 000000.090082 8 SELECT "得意先CD", "支払金額"
CHAPTER02_COMPARE_7 1 000000.001468 16 result BOOLEAN := TRUE;
CHAPTER02_COMPARE_7 1 000000.001269 18 OPEN "c_請求";
CHAPTER02_COMPARE_7 1 000000.000796 19 OPEN "c_支払";
CHAPTER02_COMPARE_7 100001 003139.033220 22 FETCH "c_請求" INTO "r_請求";
CHAPTER02_COMPARE_7 100001 003167.038783 23 FETCH "c_支払" INTO "r_支払";
CHAPTER02_COMPARE_7 100001 000259.166422 26 IF "c_請求"%FOUND != "c_支払"%FOUND THEN
CHAPTER02_COMPARE_7 100001 000094.080652 31 EXIT WHEN "c_請求"%NOTFOUND OR "c_支払"%NOTFOUND;
CHAPTER02_COMPARE_7 100001 000108.138628 34 IF "r_請求"."得意先CD" != "r_支払"."得意先CD" THEN
CHAPTER02_COMPARE_7 100001 000076.778788 40 IF "r_請求"."請求金額" != "r_支払"."支払金額" THEN
CHAPTER02_COMPARE_7 1 000000.000601 41 result := FALSE;
CHAPTER02_COMPARE_7 100001 000050.846367 42 EXIT;
CHAPTER02_COMPARE_7 1 000001.080583 46 CLOSE "c_請求";
CHAPTER02_COMPARE_7 1 000000.534869 47 CLOSE "c_支払";
CHAPTER02_COMPARE_7 1 000000.002006 50 IF result THEN
CHAPTER02_COMPARE_7 2 000000.064532 53 RETURN '不一致';
「1件ずつ比較する方法」は、FETCH (値の取り出し) 回数の多さが、
パフォーマンスの悪さを招いているようです。
1件ごとに値を取り出して PL/SQL 側で演算をするのではなく、
できるだけ、SQL で種々の演算をして、結果だけを
受け取るよう工夫しなくてはいけないことが、分かります。
2.9.「実行計画」「実行統計」を採取する
次に、さまざまな集合演算や、テーブル結合の「実行計画」「実行統計」を採取して、
Oracle がどのようなアクセスパスを用いたのかを探ってみましょう。
|
実行計画の採取を指示します。
「実行計画」と「実行統計」のみ表示し、「実行結果」を表示しないようにしておきます。
SET AUTOTRACE TRACEONLY
補 足 | |
■実行結果、実行計画、実行統計の表示について
実行計画、実行統計、実行結果を "表示する/しない" は次のように指定します。
| 実行結果 | 実行計画 | 実行統計 |
SET AUTOTRACE TRACEONLY | × | ○ | ○ |
SET AUTOTRACE ON EXPLAIN | ○ | ○ | × |
SET AUTOTRACE ON STATISTICS | ○ | × | ○ |
SET AUTOTRACE ON | ○ | ○ | ○ |
|
各比較処理の SQL 部分を実行します。
SQL> SELECT 'X'
2 FROM DUAL
3 WHERE EXISTS
4 (
5 SELECT ...
・・・(省略)・・・
実行計画採取の終了を指示します。
SET AUTOTRACE OFF
このようにして採取した「実行計画」「実行統計」を以下に掲載します。
2.9.1. "LEFT JOIN" "UNION" "RIGHT JOIN"
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=4072)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
3 1 UNION-ALL
4 3 FILTER
5 4 HASH JOIN (OUTER)
6 5 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
7 5 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
8 3 FILTER
9 8 HASH JOIN (OUTER)
10 9 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
11 9 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
まず「T_請求」と「T_支払予定」を全件検索し、
それらをハッシュジョインで結合したものを WHERE 条件で抽出し、
さらに、それらの和を返しています。
各行の「Cost」「Card」「Bytes」の意味は次の通りです。
Cost | 実行にかかるコスト |
Card | 返される行数 |
Bytes | 発生する I/O 数 |
これらは、推定値であって、実際の結果ではありません。
統計
----------------------------------------------------------
0 recursive calls
10 db block gets
811 consistent gets
2 physical reads
0 redo size
388 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
各統計情報の意味は次の通りです。
recursive calls | 再帰的 SQL の発生回数 |
db block gets | INSERT/UPDATE/DELETE などで、バッファキャッシュから読み込まれたブロック数 |
consistent gets | SELECTなどで、バッファキャッシュから読み込まれたブロック数 |
physical reads | ディスクアクセスによって読み込まれたブロック数 |
redo size | REDOログに書き込まれたバイト数 |
bytes sent via SQL*Net to client | クライアントへ送られたバイト数 |
bytes received via SQL*Net from client | クライアントから受信したバイト数 |
SQL*Net roundtrips to/from client | クライアントに送受信されたメッセージ数 |
sorts (memory) | メモリ内でのソート回数 |
sorts (disk) | ディスクを使用したソート回数 |
rows processed | SQLが処理した件数 |
トータルで、823 ブロックが読み込まれたことがわかります。
2.9.2. "MINUS" "UNION" "MINUS"
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=4072)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
3 1 UNION-ALL
4 3 MINUS
5 4 SORT (UNIQUE)
6 5 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
7 4 SORT (UNIQUE)
8 7 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
9 3 MINUS
10 9 SORT (UNIQUE)
11 10 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
12 9 SORT (UNIQUE)
13 12 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
まず「T_請求」と「T_支払予定」を全件検索し、
一旦ソートしてから差を求め、
さらに、それらの和を返しています。
「実行にかかるコスト」「返される行数」「発生する I/O 数」ともに、
「2.9.1 "LEFT JOIN" "UNION" "RIGHT JOIN"」と同じです。
統計
----------------------------------------------------------
0 recursive calls
14 db block gets
811 consistent gets
885 physical reads
0 redo size
388 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
2 sorts (disk)
1 rows processed
ソートが2回行われていること、ブロックの読み込みが
「2.9.1 "LEFT JOIN" "UNION" "RIGHT JOIN"」の2倍以上であることがわかります。
2.9.3. "UNION" "MINUS" "INTERSECT"
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=4072)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
3 1 MINUS
4 3 SORT (UNIQUE) (Cost=2264 Card=444000 Bytes=2664000)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
7 5 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
8 3 INTERSECTION
9 8 SORT (UNIQUE) (Cost=566 Card=111000 Bytes=666000)
10 9 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
11 8 SORT (UNIQUE) (Cost=566 Card=111000 Bytes=666000)
12 11 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
まず「T_請求」と「T_支払予定」を全件検索し、
一旦ソートしてから積を求め、
さらに、「T_請求」と「T_支払予定」の和をソートしたものとの差を
を返しています。
「2.9.1 "LEFT JOIN" "UNION" "RIGHT JOIN"」、
「2.9.2. "MINUS" "UNION" "MINUS"」に比べて、ソート処理のコストが高くなっています。
統計
----------------------------------------------------------
0 recursive calls
25 db block gets
1621 consistent gets
1730 physical reads
0 redo size
388 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
3 sorts (disk)
1 rows processed
ソートが3回行われていること、ブロックの読み込みが
「2.9.1 "LEFT JOIN" "UNION" "RIGHT JOIN"」の4倍以上であることがわかります。
2.9.4. FULL OUTER JOIN
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=4072)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
3 1 VIEW (Cost=799 Card=138750 Bytes=3607500)
4 3 UNION-ALL
5 4 FILTER
6 5 HASH JOIN (OUTER)
7 6 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
8 6 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
9 4 HASH JOIN (ANTI) (Cost=316 Card=27750 Bytes=333000)
10 9 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=55500 Bytes=333000)
11 9 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
まず「T_請求」と「T_支払予定」を全件検索し、ハッシュジョインで結合したものから
WHERE 条件で抽出したものと、
「T_請求」と「T_支払予定」を全件検索し、ハッシュジョインで結合したものとの
和を返しています。
アンチジョインのコスト、ビューのコストが高くなっています。
統計
----------------------------------------------------------
0 recursive calls
10 db block gets
811 consistent gets
0 physical reads
0 redo size
388 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
トータルで、821 ブロックが読み込まれたことが、わかります。
これまでで最も低い値です。
2.9.5. "UNION" + グループ化
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=4072)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
3 1 FILTER
4 3 SORT (GROUP BY) (Cost=1820 Card=222000 Bytes=8658000)
5 4 VIEW (Cost=124 Card=222000 Bytes=8658000)
6 5 UNION-ALL
7 6 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
8 6 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
まず「T_請求」と「T_支払予定」を全件検索し、それらの和をソートし、
WHERE 条件で抽出しています。
これまでの方法では、両テーブルを2回ずつ読んでいたのに対して、
この方法では1回ずつで済んでいます。
ただし、グループ化のコストが高くなっています。
統計
----------------------------------------------------------
0 recursive calls
13 db block gets
811 consistent gets
1018 physical reads
0 redo size
388 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1 rows processed
テーブルの読み込みは1回ずつで済んでいるにもかかわらず、ブロックの読み込みが
「2.9.1 "LEFT JOIN" "UNION" "RIGHT JOIN"」
「2.9.4. FULL OUTER JOIN」の2倍以上であることがわかります。
2.9.6. 集合の要素数を数える
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000)
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000)
実行計画
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=483 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=483 Card=55500 Bytes=666000)
3 2 TABLE ACCESS (FULL) OF 'T_請求' (Cost=62 Card=111000 Bytes=666000)
4 2 TABLE ACCESS (FULL) OF 'T_支払予定' (Cost=62 Card=111000 Bytes=666000)
共通部分を求める時にハッシュジョインを使っていますが、そのコストが高くなっています。
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
405 consistent gets
0 physical reads
0 redo size
395 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
405 consistent gets
0 physical reads
0 redo size
395 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
統計
----------------------------------------------------------
0 recursive calls
8 db block gets
810 consistent gets
819 physical reads
0 redo size
394 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
トータルでは、ブロックの読み込みが
「2.9.1 "LEFT JOIN" "UNION" "RIGHT JOIN"」
「2.9.4. FULL OUTER JOIN」の3倍以上になることがわかります。
終わりに
「テーブル内容の比較」を題材に、さまざまなテーブル結合、集合演算、カーソルを使った処理について
見てきましたが、パフォーマンスを左右するのは、フェッチ回数と、ディスク I/O の数で
あることが、はっきりと認識できたと思います。
|