The INTERSECT operator computes the intersection of the outputs of its left and right child operators, removing duplicates.
OceanBase AI Database supports the MERGE INTERSECT DISTINCT and HASH INTERSECT DISTINCT operators.
MERGE INTERSECT DISTINCT
In the following example, Q1 uses INTERSECT to join the two queries. Column c1 has a usable sort key, so Operator 0 generates a MERGE INTERSECT DISTINCT operation to find the intersection and remove duplicates. Since column c2 lacks a usable sort key, a SORT operator is assigned to Operator 3 for sorting. 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 intersection result after deduplication.
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 c1 FROM t1 INTERSECT SELECT c2 FROM t1;
Query Plan:
=================================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-------------------------------------------------
|0 |MERGE INTERSECT DISTINCT| |2 |77 |
|1 | TABLE SCAN |T1 |2 |37 |
|2 | SORT | |2 |39 |
|3 | TABLE SCAN |T1 |2 |37 |
=================================================
Outputs & filters:
-------------------------------------
0 - output([INTERSECT(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 INTERSECT operators as follows:
Information name |
Description |
|---|---|
| output | The output expressions of this operator. The two child operators joined by INTERSECT correspond to one column in the intersection 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 INTERSECT operator, so the value is nil. |
HASH INTERSECT DISTINCT
As shown in the following example, Q2 uses INTERSECT to join the two queries. With no available sort key, Operator 0 employs HASH INTERSECT DISTINCT to find the intersection and remove duplicates. During execution, the operator first reads the output from one child node to build a hash table and remove duplicates, then reads from the other child node and uses the hash table to compute the intersection and remove duplicates.
Q2:
obclient> EXPLAIN SELECT c2 FROM t1 INTERSECT SELECT c2 FROM t1;
Query Plan:
================================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------------------
|0 |HASH INTERSECT DISTINCT| |2 |77 |
|1 | TABLE SCAN |T1 |2 |37 |
|2 | TABLE SCAN |T1 |2 |37 |
================================================
Outputs & filters:
-------------------------------------
0 - output([INTERSECT(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 INTERSECT DISTINCT operator. The field meanings are the same as those for the MERGE INTERSECT DISTINCT operator.