The EXCEPT operator performs a set difference operation on the output sets of its left and right child operators and removes duplicates.
OceanBase AI Database does not distinguish between EXCEPT and MINUS; either can be used as the keyword for the set difference operation.
OceanBase AI Database supports the MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT operators.
MERGE EXCEPT DISTINCT
In the following example, query Q1 uses MINUS to combine the two queries. Because column c1 has an available sort key, Operator 0 generates a MERGE EXCEPT DISTINCT operator to compute the set difference and remove duplicates. Because column c2 does not have an available sort key, a SORT operator is assigned to Operator 3. During execution, the operator reads the ordered inputs from the left and right child nodes, performs a MERGE using the sorted inputs, and produces the result set after removing duplicates.
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)
In the preceding example, the outputs & filters section of the execution plan shows the output information of the EXCEPT operator as follows:
Information name |
Description |
|---|---|
| output | The output expressions of this operator. The two child operators joined by EXCEPT/MINUS correspond to one column in the set difference result. The content in parentheses specifies the output columns of the left and right child nodes for this column. |
| filter | The filter predicate of this operator. In the example, no filter is set for the EXCEPT operator, so the value is nil. |
HASH EXCEPT DISTINCT
In the following example, query Q2 uses MINUS to combine the two queries. No sort key is available, so Operator 0 uses HASH EXCEPT DISTINCT to compute the set difference and remove duplicates. During execution, the operator first reads the output from the left child node to build a hash table and remove duplicates, then reads the output from the right child node, and uses the hash table to compute the set difference and remove duplicates.
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)
The outputs & filters section of the execution plan for the preceding example shows the output information of the HASH EXCEPT DISTINCT operator. The field meanings are the same as those for the MERGE EXCEPT DISTINCT operator.