タイトルロゴ

記載日 2006/02/19
はじめに

 集合演算子は、二つのクエリーを用いた検索結果セットに対して、論理的な 和、差、積を実施する演算子です。OracleのSQLで用意されている集合演算子を以下の表にまとめました。

UNION ALL 二つの結果セットの和を返します。重複した結果を省きません。検索結果はソートされません。
UNION 二つの検索結果セットの和を返します。重複した結果は省かれ、検索結果はソートされます。
MINUS 二つの検索結果セットの差を返します。一つ目のクエリーの検索結果セットから、二つ目のクエリーの検索結果セットが除かれます。重複した結果は省かれ、検索結果はソートされます。
INTERSECT 二つの検索結果セットの積を返します。一つ目のクエリーの検索結果セットと、二つ目のクエリーの検索結果セットで同じ結果のみを返します。重複した結果は省かれ、検索結果はソートされます。

(※上の表からわかる通り、UNION ALLは 重複した結果の扱い方や検索結果の表示順において他の集合演算子とは異なった動作をします。 その為、他の集合演算子とは別物扱いされることがあります。)

 大規模データベースシステムでは、集合演算子を使用する機会が増えます。 理由は、ディスクI/Oの軽減を図る目的で、データのキーに着目して格納先のテーブルを 明示的に割り振る、行方向へのテーブル分解 が適応される場合があるからです。この方針を採用した場合、 データを格納する為のテーブルは複数個に分けて定義され、データ挿入時には、そのデータ をどのテーブルに格納すればよいか、キー項目をもとに毎回割り振る必要が出てきます。 この様なデータ管理方法を行っていても、キー項目とは無関係な全てのデータを検索対象とした SELECT文が必要になる場合がどうしても出てきます。 そこで、集合演算子を使用する機会が増えるのです (※データベース設計者は、大規模データベースのテーブル構成を考える際に、 行方向へのテーブル分解行うか、Oracleのパーティショニング機能を 採用すべきか迷ったはずです。どちらの方針が良いのかはケース・バイ・ケースですが、これについての 議論は、また、別の機会に設けさせて頂きます。)

 Oracleに用意されている集合演算子の実例紹介と、使用するに当たっての注意事項をまとめてみたいと思います。



セクション











検証環境

 
ページのTOPへ

 以下の出身地テーブル(HOMETOWN_ADDRESS_T)と現住所テーブル(PRESENT_ADDRESS_T)が存在しているとします。 これらを用いて、集合演算子の実例を紹介します。

HOMETOWN_ADDRESS_T
NAME HOMETOWN_ADDRESS
Takeshi Suzuki Tokyo
Mitsuo Watanabe Osaka
Hisashi Gotou Osaka
Taro Ueda Fukuoka
Akira Hashimoto Miyagi
PRESENT_ADDRESS_T
NAME PRESENT_ADDRESS
Takeshi Suzuki Tiba
Mitsuo Watanabe Kyoto
Hisashi Gotou Osaka
Taro Ueda Tiba
Akira Hashimoto Tokyo


■ UNION ALLの実行例を以下に示します。
 以下の実行例では、出身地テーブル(HOMETOWN_ADDRESS_T)と現住所テーブル(PRESENT_ADDRESS_T)から、 両方のテーブルに登録されている都道府県名を一つにまとめて取得するSELECT文になっています。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
  2 UNION ALL
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Tokyo
Osaka
Osaka
Fukuoka
Miyagi
Tiba
Kyoto
Osaka
Osaka
Tokyo

10 rows selected.

SQL> SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// @
  2 UNION ALL
  3 SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Tiba
Kyoto
Osaka
Osaka
Tokyo
Tokyo
Osaka
Osaka
Fukuoka
Miyagi

10 rows selected.

SQL>

 検索結果は、@の検索結果にAの検索結果が連結する形で表示されています。 重複する検索結果が取除かれることも無ければ、検索結果の返される順番もソートされることがありません。 UNION ALLを使用した時の実行計画も確認しておきましょう(※オプティマイザはコストベースです。)。

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   UNION-ALL
   2    1     TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   3    1     TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

 実行計画にUNION-ALLが表示されています。これは、

     ・TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
     ・TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

の二つの結果を連結していることを表しています。

■ UNIONの実行例を以下に示します。
 以下の実行例では、出身地テーブル(HOMETOWN_ADDRESS_T)と現住所テーブル(PRESENT_ADDRESS_T)から、 両方のテーブルに登録されている都道府県名の種類を一つにまとめて取得するSELECT文になっています。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
    UNION
    SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Fukuoka
Kyoto
Miyagi
Osaka
Tiba
Tokyo

6 rows selected.

SQL> SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// @
    UNION
    SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Fukuoka
Kyoto
Miyagi
Osaka
Tiba
Tokyo

6 rows selected.

SQL>

 検索結果は、@の検索結果とAの検索結果を混ぜてソートし、重複する検索結果が取除かれて います。故に、@とAの検索文を入替えても同じ結果が返されました。 UNIONを使用した時の実行計画も確認しておきましょう(※オプティマイザはコストベースです。)。

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   4    2       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

 実行計画にUNION-ALLが表示されています。これは、

     ・TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
     ・TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

の二つの結果を連結していることを表しています。この連結結果に対して、ソートが行われていることを 表すSORT (UNIQUE)が表示されています。UNIONは実行時にUNION ALLを 内部的に実行し、その結果をソートして重複する結果を取り除くと言う動きをしていることがわかります。

■ MINUSの実行例を以下に示します。
 以下の実行例では、前者は、出身地テーブル(HOMETOWN_ADDRESS_T)にのみ登録されてい る都道府県名の種類を調べるSQLになっており、後者は、現住所テーブル(PRESENT_ADDRESS_T)に のみ登録されている都道府県名の種類をそれぞれ調べるSQLになっています。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
  2 MINUS
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Fukuoka
Miyagi

2 rows selected.

SQL> SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// @
  2 MINUS
  3 SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Kyoto
Tiba

2 rows selected.

SQL>

 検索結果は、@の検索結果から、Aの検索結果に含まれるものを取り除き、重複する検索結果が取除かれて います。当然ですが、@とAの検索文を入替えると異なる結果が返されました。 MINUSを使用した時の実行計画も確認しておきましょう(※オプティマイザはコストベースです。)。
Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MINUS
   2    1     SORT (UNIQUE)
   3    2       TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   4    1     SORT (UNIQUE)
   5    4       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

 実行計画に、SORT (UNIQUE)が二回表示されています。 これは、@とAの検索結果に対して、個別にソート処理と重複結果除去が 行われていることを表しています。

 その後、@の検索結果からAの検索結果が引かれることを表す、MINUSが実行計画に 現れます。

■ INTERSECTの実行例を以下に示します。
 以下の実行例では、出身地テーブル(HOMETOWN_ADDRESS_T)と現住所テーブル(PRESENT_ADDRESS_T)の両方に 登録されている都道府県名の種類を調べるSELECT文になっています。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
  2 INTERSECT
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Osaka
Tokyo

2 rows selected.

SQL> SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// @
  2 INTERSECT
  3 SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T; --// A

ADDRESS
------------------------------------------------------------
Osaka
Tokyo

2 rows selected.

SQL>

 検索結果は、@の検索結果とAの検索結果の両方に含まれるものを重複行を取除いて表示されます。 当然ですが、@とAの検索文を入替えても同じ結果が返されました。 INTERSECTを使用した時の実行計画も確認しておきましょう(※オプティマイザはコストベースです。)。

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INTERSECTION
   2    1     SORT (UNIQUE)
   3    2       TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   4    1     SORT (UNIQUE)
   5    4       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

 実行計画に、SORT (UNIQUE)が二回表示されています。 これは、@とAの検索結果に対して、個別にソート処理と重複結果除去が 行われていることを表しています。

 その後、@の検索結果とAの検索結果の共通部分を取出す、INTERSECTIONが 実行計画に現れます。


 
ページのTOPへ

 Oracleでは、異なるデータ型間の演算において、型を変換した際に意味のある演算であれば暗黙のうちに型を変換 する暗黙の型変換と言う機能があります。しかし、集合演算子を用いた問い合わせでは この暗黙の型変換が行われません。以下の様に、暗黙の型変換を期待した集合演算は、ことごとくエラーになります。

SQL> SELECT 1 FROM DUAL
  2 UNION ALL
  3 SELECT '9' FROM DUAL;
SELECT 1 FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

SQL> SELECT '10' FROM DUAL
  2 UNION
  3 SELECT TO_BINARY_FLOAT(3) FROM DUAL;
SELECT '10' FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

SQL>

 NULLは他のデータ型との集合演算において有効です。しかし、TO_NUMBER( )関数やTO_CHAR( )関数、CAST( )関数等の変換関数 を用いて、NULLをキャストしている場合、NULLは型を持つ値であると判断されてしまい、 エラーになってしまいます。NULLが型を持つとはおかしな話ですが、OracleはSQL文の実行前に構文エラー として、このエラーを出しています。TO_NUMBER(NULL)の結果がNULLかどうかは、Oracleも実行してみるまでわからない と言うことですね。

SQL> SELECT 'A' FROM DUAL
  2 UNION ALL
  3 SELECT NULL FROM DUAL;

'
-
A
   ←ここにNULLが表示されています。正常に検索できます。

2 rows selected.  

SQL> SELECT 'A' FROM DUAL
  2 UNION ALL
  3 SELECT TO_NUMBER(NULL) FROM DUAL;
SELECT 'A' FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression ←エラーになります。

SQL> SELECT 'A' FROM DUAL
  2 UNION ALL
  3 SELECT CAST(NULL AS NUMBER) FROM DUAL;
SELECT 'A' FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression ←エラーになります。

SQL>


 
ページのTOPへ

■ 二つのUNIONを用いた集合演算の実行計画
 二つのUNIONを用いた集合演算はどの様な実行計画になるか調べてみました。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
  2 UNION
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// A
  4 UNION
  5 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// B

ADDRESS
------------------------------------------------------------
Fukuoka
Kyoto
Miyagi
Osaka
Tiba
Tokyo

6 rows selected.

SQL>

 検索結果は、@〜Bの検索結果がソートされ、重複データが取除かれた結果になりました。

 私は、実行計画が以下の様になるのではないかと予想していました。それは、 @とAのUNIONが実行され、その結果セットに対して、BのUNIONが実行されると 思っていたからです。
Execution Plan
-----------------------------------------------------------------------------------
   *      SELECT STATEMENT Optimizer=CHOOSE
   *    *   SORT (UNIQUE)
   *    *     UNION-ALL
   *    *       SORT (UNIQUE)
   *    *         UNION-ALL
   *    *           TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   *    *           TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)
   *    *       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)
 しかし、実際の実行計画は以下の様になっていました(※オプティマイザはコストベースです。)。

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   4    2       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)
   5    2       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

 SORT (UNIQUE)が一度しか出てきていません。 @〜BまでのUNION ALLが実行され、最終的に一回だけ ソート処理と重複結果除去が行われていることを表しています。 検索文に含まれる最初のUNIONはUNION ALLとして解釈されていることになります。

 つまり、UNIONを二つ用いた上で紹介した検索文と、次の検索文は、SQLの実行計画上 全く同じ意味になります。実際に実行計画を調べましたが、全く同じでした。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T
  2 UNION ALL
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T
  4 UNION
  5 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T;

ADDRESS
------------------------------------------------------------
Fukuoka
Kyoto
Miyagi
Osaka
Tiba
Tokyo

6 rows selected.

SQL>

■ DISTINCTとUNIONを用いた集合演算の実行計画
 集合演算子ではありませんが、SQL文でDISTINCT句を用いると、 検索結果に対するソート処理と、 重複結果を取除く処理が走ります。

SQL> SELECT DISTINCT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T;

ADDRESS
------------------------------------------------------------
Fukuoka
Miyagi
Osaka
Tokyo

4 rows selected.

SQL>

 DISTINCT句を用いた場合の実行計画は以下の様になっています(※オプティマイザはコストベースです。)。 UNIONと同じように、SORT (UNIQUE)が実行計画に現れています。

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)

 DISTINCT句と集合演算子を混合使用した場合、どのタイミングでSORT (UNIQUE)を実行すればよいか、 オプティマイザが自動で判別し、最も効率的と考えられる実行計画 が立てられます。

SQL> SELECT DISTINCT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
  2 UNION
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// A
  4 UNION
  5 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// B

ADDRESS
------------------------------------------------------------
Fukuoka
Kyoto
Miyagi
Osaka
Tiba
Tokyo

6 rows selected.

SQL>

 実行計画にはSORT (UNIQUE)が一度しか出てきていません。

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   4    2       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)
   5    2       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)

 参考までに、以下のSQLとその実行計画も確認して下さい。これは、上のSQLとは全く意味の異なるSQLなので、 検索結果も実行計画も異なります。

SQL> SELECT HOMETOWN_ADDRESS ADDRESS FROM HOMETOWN_ADDRESS_T --// @
  2 UNION
  3 SELECT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T --// A
  4 UNION ALL
  5 SELECT DISTINCT PRESENT_ADDRESS ADDRESS FROM PRESENT_ADDRESS_T; --// B

ADDRESS
------------------------------------------------------------
Fukuoka
Kyoto
Miyagi
Osaka
Tiba
Tokyo
Kyoto
Osaka
Tiba
Tokyo

10 rows selected.

SQL>

 実行計画は以下の様になっています(※オプティマイザはコストベースです。)。 最初に、@とAのUNIONが実行されます。その後、BのDISTINCTが実行され、先の@とAのUNION結果に UNION ALLされます。

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   UNION-ALL
   2    1     SORT (UNIQUE)
   3    2       UNION-ALL
   4    3         TABLE ACCESS (FULL) OF 'HOMETOWN_ADDRESS_T' (TABLE)
   5    3         TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)
   6    1     SORT (UNIQUE)
   7    6       TABLE ACCESS (FULL) OF 'PRESENT_ADDRESS_T' (TABLE)


 
ページのTOPへ

 Oracle DatabaseのSQLリファレンス(※Oracle Database SQL リファレンス 10g リリース2(10.2)) を見ると「SQL 規格に準拠するために、Oracle の今後のリリースでは、他の集合演算子より優先順位の高いINTERSECT 演算子が提供されます。 」と記載されています。これは、Oracleの今後のバージョンアップで、集合演算子に優先順位が付けられることを 言っています。一つのSQLで複数の集合演算子を使用する場合は、INTERSECT 演算子を使用している使用していないに関わらず、 括弧を付けてコーディングすべきだと言うことです。


 
ページのTOPへ
 集合演算子では、排他的論理和が用意されていません。 UNIONした結果からINTERSECTした結果をMINUSすることで実現できます。
 集合演算子を使用したSQL文では、左にあるSQL文から順番に一つずつ実行 されますが、ソート処理を必要とする演算子と混合使用した場合は、ソートを実行するタイミングが最適化 されます。