EXCEPT 演算子は、左右のサブノード演算子の出力集合に対して差集合演算を行い、重複を削除します。
Oracleモードでは一般的に MINUS を使用して差集合演算を行い、MySQLモードでは一般的に EXCEPT を使用して差集合演算を行います。OceanBaseデータベースのMySQLモードでは EXCEPT と MINUS の区別はなく、どちらも差集合演算のキーワードとして使用できます。
OceanBaseデータベースがサポートする EXCEPT 演算子には MERGE EXCEPT DISTINCT と HASH EXCEPT DISTINCT が含まれます。
MERGE EXCEPT DISTINCT
以下の例では、Q1が2つのクエリに対してMINUSを使用して結合し、c1列にはソート可能なデータがあります。演算子0はMERGE EXCEPT DISTINCTを生成して差集合を求め、重複を削除します。c2列にはソート可能なデータがないため、演算子3にSORT演算子が割り当てられてソートが実行されます。演算子の実行時には、左右のサブノードから順序付き入力を読み取り、順序付き入力を利用してMERGEを行い、重複を削除して差集合の結果を得ます。
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT /*+NO_USE_HASH_AGGREGATION*/ c1 FROM t1 MINUS SELECT c2 FROM t1;
Query Plan:
==============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------------------
|0 |MERGE EXCEPT DISTINCT| |2 |77 |
|1 | TABLE SCAN |T1 |2 |37 |
|2 | SORT | |2 |39 |
|3 | TABLE SCAN |T1 |2 |37 |
==============================================
Outputs & filters:
-------------------------------------
0 - output([MINUS(T1.C1, T1.C2)]), filter(nil)
1 - output([T1.C1]), filter(nil),
access([T1.C1]), partitions(p0)
2 - output([T1.C2]), filter(nil), sort_keys([T1.C2, ASC])
3 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
上記の例では、実行計画表示のoutputs & filtersにおいて、EXCEPT演算子の出力情報が詳細に示されています。
| 情報名 | 意味 |
|---|---|
| output | この演算子の出力式。EXCEPT/MINUS で結合された2つのサブノード演算子に対応する出力（Oracleモードでは MINUS、MySQLモードでは EXCEPT）を表し、差集合演算の出力結果の1列を示します。括弧内は左右のサブノードが対応するこの列の出力列です。 |
| filter | この演算子上のフィルタ条件。例では EXCEPT 演算子に filter が設定されていないため、nil となります。 |
HASH EXCEPT DISTINCT
以下の例では、Q2は2つのクエリに対してMINUSを使用して結合し、ソートを利用できません。演算子0はHASH EXCEPT DISTINCTを使用して差集合を求め、重複を削除します。演算子の実行時には、まず左側のサブノードの出力を読み込んでハッシュテーブルを作成し、重複を削除します。次に、右側のサブノードの出力を読み込み、ハッシュテーブルを使用して差集合を求め、重複を削除します。
Q2:
obclient> EXPLAIN SELECT c2 FROM t1 MINUS SELECT c2 FROM t1;
Query Plan:
=============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |HASH EXCEPT DISTINCT| |2 |77 |
|1 | TABLE SCAN |T1 |2 |37 |
|2 | TABLE SCAN |T1 |2 |37 |
=============================================
Outputs & filters:
-------------------------------------
0 - output([MINUS(T1.C2, T1.C2)]), filter(nil)
1 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
2 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
上記の例の実行計画表示におけるoutputs & filtersは、HASH EXCEPT DISTINCT演算子の出力情報を詳細に示しています。フィールドの意味はMERGE EXCEPT DISTINCT演算子と同じです。