EXCEPT/MINUS

EXCEPT operators perform a difference set operation on the output of the left and right suboperators and deduplicate the result.

Generally, MINUS operators are used in Oracle mode to perform difference set operations, whereas EXCEPT operators are used in MySQL mode to perform difference set operations. In the MySQL mode of OceanBase Database, however, the EXCEPT and MINUS operators are not distinguished and both of them can be used for difference set operations.

OceanBase Database supports the following types of EXCEPT operators: MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT.

MERGE EXCEPT DISTINCT

In the following example, Q1 joins the two queries with MINUS. Since c1 is sorted, the MERGE EXCEPT DISTINCT operator is used as Operator 0 to obtain the difference set and deduplicate the result. However, c2 is not sorted, so a SORT operator is assigned before Operator 3 for sorting. When these operators are executed, they read sorted inputs from the left and right subnodes and merge and deduplicate the rows to generate the difference set.

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 /*+NO_USE_HASH_AGGREGATION*/ SELECT 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)

In the preceding example, the Outputs & filters section shows in detail the output information of the MERGE EXCEPT DISTINCT operator.

HASH EXCEPT DISTINCT

In the following example, Q2 uses the MINUS operator to join the two queries, no sorting method is available, and the HASH EXCEPT DISTINCT operator is used as Operator 0 to generate the difference set and deduplicate the result. When the operator is being executed, it first reads the output of the left subnode to create a hash table and removes duplicate rows. Then, it reads the output of the right subnode and uses the hash table to generate the difference set and deduplicate the result.

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)

In the preceding example, the Outputs & filters section shows in detail the output information of the HASH EXCEPT DISTINCT operator. The fields of the operator have the same meaning as those of the MERGE EXCEPT DISTINCT operator.