The EXCHANGE operator is used for data exchange between threads.
The EXCHANGE operator is suitable for distributed scenarios and typically appears in pairs, with an OUT operator at the data source and an IN operator at the destination.
EXCH-IN/OUT
EXCH-IN/OUT, also known as EXCHANGE IN/ EXCHANGE OUT, is used to aggregate data from multiple partitions and send it to the primary node where the query resides.
As shown in the following example, the query accesses data from five partitions (p0-p4). Operator 1 receives the output from Operator 2 and sends the data out; Operator 0 receives the outputs generated by Operator 1 across multiple partitions and aggregates them for output.
obclient> CREATE TABLE t15 (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 5;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t15;
+---------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |20 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |20 | |
| |2 | └─PX PARTITION ITERATOR| |1 |19 | |
| |3 | └─TABLE FULL SCAN |t15 |1 |19 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t15.c1, t15.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t15.c1, t15.c2)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([t15.c1], [t15.c2]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([t15.c1], [t15.c2]), filter(nil), rowset=16 |
| access([t15.c1], [t15.c2]), partitions(p[0-4]) |
| is_index_back=false, is_global_index=false, |
| range_key([t15.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------+
In the preceding example, the outputs & filters section of the execution plan shows the output information of the EXCH-IN/OUT operator as follows:
Information name |
Description |
|---|---|
| PX COORDINATOR | A special type of EXCHANGE IN operator. In addition to pulling data from remote nodes, it is also responsible for scheduling the execution of subplans. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the EXCH-IN/OUT operator, so the value is nil. |
EXCH-IN/OUT (REMOTE)
The EXCH-IN/OUT (REMOTE) operator is used to pull remote data (data from a single partition) back to the local machine.
As shown in the following example, a non-partitioned table is created on server A, and a query is executed on server B to read data from this table. Since the data to be read is remote, the execution plan assigns Operator 0 and Operator 1 to fetch the remote data. Operator 1 executes on server A, reads data from the t14 table, and sends the data out; Operator 0 executes on server B, receiving the output generated by Operator 1.
obclient> CREATE TABLE t14 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t14;
+--------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------+
| ===================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------- |
| |0 |EXCHANGE IN REMOTE | |1 |5 | |
| |1 |└─EXCHANGE OUT REMOTE| |1 |4 | |
| |2 | └─TABLE FULL SCAN |t14 |1 |3 | |
| ===================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t14.C1], [t14.C2]), filter(nil) |
| 1 - output([t14.C1], [t14.C2]), filter(nil) |
| 2 - output([t14.C1], [t14.C2]), filter(nil), rowset=16 |
| access([t14.C1], [t14.C2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t14.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------+
The execution plan for the preceding example shows the output information of the EXCH-IN/OUT (REMOTE) operator in the outputs & filters section. The field meanings are the same as those for the EXCH-IN/OUT operator.
EXCH-IN/OUT (PKEY)
The EXCH-IN/OUT (PKEY) operator is used for data repartitioning. It is commonly used in binary operators to repartition the data of child nodes on one side according to the partitioning method of the child nodes on the other side.
In the following example, the query joins data from two partitioned tables. The execution plan repartitions the data from table s15 according to the partitioning method of table t13. The input to Operator 4 is the result of scanning table s15. For each row in s15, this operator determines which node the row should be sent to based on the data partitioning of t13 and the join conditions of the query.
Additionally, you can see that Operator 3 is an EXCHANGE IN DISTR, which is a special EXCHANGE IN operator. When summarizing data from multiple partitions, it performs a certain degree of merge sort. In this execution plan, the data received by Operator 3 for each partition is already sorted in order of column c1. It merges and sorts each piece of received data, ensuring the final output is also sorted in order of column c1.
obclient> CREATE TABLE t13 (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 5;
Query OK, 0 rows affected
obclient> CREATE TABLE s15 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM s15, t13 WHERE s15.c1 = t13.c1;
+-------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------+
| ===================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |38 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001|1 |37 | |
| |2 | └─HASH JOIN | |1 |36 | |
| |3 | ├─EXCHANGE IN DISTR | |1 |17 | |
| |4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |16 | |
| |5 | │ └─PX PARTITION ITERATOR | |1 |16 | |
| |6 | │ └─TABLE FULL SCAN |s15 |1 |16 | |
| |7 | └─PX PARTITION ITERATOR | |1 |19 | |
| |8 | └─TABLE FULL SCAN |t13 |1 |19 | |
| ===================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(s15.c1, s15.c2, t13.c1, t13.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(s15.c1, s15.c2, t13.c1, t13.c2)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([s15.c1], [t13.c1], [s15.c2], [t13.c2]), filter(nil), rowset=16 |
| equal_conds([s15.c1 = t13.c1]), other_conds(nil) |
| 3 - output([s15.c1], [s15.c2]), filter(nil), rowset=16 |
| 4 - output([s15.c1], [s15.c2]), filter(nil), rowset=16 |
| (#keys=1, [s15.c1]), dop=1 |
| 5 - output([s15.c1], [s15.c2]), filter(nil), rowset=16 |
| force partition granule |
| 6 - output([s15.c1], [s15.c2]), filter(nil), rowset=16 |
| access([s15.c1], [s15.c2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([s15.c1]), range(MIN ; MAX)always true |
| 7 - output([t13.c1], [t13.c2]), filter(nil), rowset=16 |
| affinitize, force partition granule |
| 8 - output([t13.c1], [t13.c2]), filter(nil), rowset=16 |
| access([t13.c1], [t13.c2]), partitions(p[0-4]) |
| is_index_back=false, is_global_index=false, |
| range_key([t13.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------------------------+
In the preceding example, the outputs & filters section of the execution plan shows the output information of the EXCH-IN/OUT (PKEY) operator as follows:
Information name |
Description |
|---|---|
| PX COORDINATOR | A special type of EXCHANGE IN operator. In addition to pulling data from remote nodes, it is also responsible for scheduling the execution of subplans. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the EXCH-IN/OUT (PKEY) operator, so the value is nil. |
| pkey | The column used for repartitioning. For example, #keys=1, [s15.c1] indicates repartitioning by column c1. |
EXCH-IN/OUT (HASH)
The EXCH-IN/OUT (HASH) operator is used to repartition data using a set of hash functions.
In the execution plan shown below, Operators 3-5 and 7-8 are two groups of EXCHANGE operators that use hash repartitioning. The role of these two groups of operators is to scatter the data from tables t12 and s14 into multiple parts using a new set of hash functions. In the example, the hash columns are t12.c2 and s14.c2, which ensures rows with the same value in column c2 are distributed into the same part. Based on the repartitioned data, Operator 2 HASH JOIN then joins each part of data according to the condition t12.c2= s14.c2.
Furthermore, since a parallelism of 2 is executed in the query, the plan shows dop = 2 (DOP stands for Degree of Parallelism).
obclient> CREATE TABLE t12 (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> CREATE TABLE s14 (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+PARALLEL(2) LEADING(t12 s14) USE_HASH(s14) PQ_DISTRIBUTE(s14 HASH HASH)*/ * FROM t12, s14 WHERE t12.c2 = s14.c2;
+-------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------+
| ===================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |18 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10002|1 |17 | |
| |2 | └─HASH JOIN | |1 |17 | |
| |3 | ├─EXCHANGE IN DISTR | |1 |9 | |
| |4 | │ └─EXCHANGE OUT DISTR (HASH)|:EX10000|1 |8 | |
| |5 | │ └─PX BLOCK ITERATOR | |1 |8 | |
| |6 | │ └─TABLE FULL SCAN |t12 |1 |8 | |
| |7 | └─EXCHANGE IN DISTR | |1 |9 | |
| |8 | └─EXCHANGE OUT DISTR (HASH)|:EX10001|1 |8 | |
| |9 | └─PX BLOCK ITERATOR | |1 |8 | |
| |10| └─TABLE FULL SCAN |s14 |1 |8 | |
| ===================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t12.c1, t12.c2, s14.c1, s14.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t12.c1, t12.c2, s14.c1, s14.c2)]), filter(nil), rowset=16 |
| dop=2 |
| 2 - output([t12.c2], [s14.c2], [t12.c1], [s14.c1]), filter(nil), rowset=16 |
| equal_conds([t12.c2 = s14.c2]), other_conds(nil) |
| 3 - output([t12.c2], [t12.c1]), filter(nil), rowset=16 |
| 4 - output([t12.c2], [t12.c1]), filter(nil), rowset=16 |
| (#keys=1, [t12.c2]), dop=2 |
| 5 - output([t12.c1], [t12.c2]), filter(nil), rowset=16 |
| 6 - output([t12.c1], [t12.c2]), filter(nil), rowset=16 |
| access([t12.c1], [t12.c2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([t12.__pk_increment]), range(MIN ; MAX)always true |
| 7 - output([s14.c2], [s14.c1]), filter(nil), rowset=16 |
| 8 - output([s14.c2], [s14.c1]), filter(nil), rowset=16 |
| (#keys=1, [s14.c2]), dop=2 |
| 9 - output([s14.c1], [s14.c2]), filter(nil), rowset=16 |
| 10 - output([s14.c1], [s14.c2]), filter(nil), rowset=16 |
| access([s14.c1], [s14.c2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([s14.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------------------------+
Among them, the PX PARTITION ITERATOR operator is used to iterate data at the partition granularity. For more information, see GI.
In the execution plan of the preceding example, the outputs & filters section shows the output information of the EXCH-IN/OUT (HASH) operator as follows:
Information name |
Description |
|---|---|
| PX COORDINATOR | A special type of EXCHANGE IN operator. In addition to pulling data from remote nodes, it is also responsible for scheduling the execution of subplans. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the EXCH-IN/OUT (HASH) operator, so the value is nil. |
| pkey | The column used for hash-based repartitioning. For example, #keys=1, [s14.c2] indicates that data is hashed and redistributed by column c2. |
EXCH-IN/OUT (BC2HOST)
The EXCH-IN/OUT (BC2HOST) operator is used to repartition input data using the BC2HOST method. It broadcasts data to other threads.
In the execution plan of the following example, operators 3 and 4 are a pair of EXCHANGE operators that use the BC2HOST repartitioning method. They broadcast the data from table t11 to each thread. Each partition of table s13 attempts to join with the broadcasted data from table t11.
obclient> CREATE TABLE t11 (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> CREATE TABLE s13 (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> INSERT INTO s VALUES (1, 1), (2, 2), (3, 3), (4, 4);
Query OK, 1 rows affected
obclient> EXPLAIN SELECT /*+PARALLEL(2) */ * FROM t11, s13 WHERE t11.c2 = s13.c2;
+-------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------+
| ======================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------ |
| |0 |PX COORDINATOR | |1 |18 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001|1 |17 | |
| |2 | └─SHARED HASH JOIN | |1 |17 | |
| |3 | ├─EXCHANGE IN DISTR | |1 |9 | |
| |4 | │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10000|1 |8 | |
| |5 | │ └─PX BLOCK ITERATOR | |1 |8 | |
| |6 | │ └─TABLE FULL SCAN |t11 |1 |8 | |
| |7 | └─PX BLOCK ITERATOR | |4 |8 | |
| |8 | └─TABLE FULL SCAN |s13 |4 |8 | |
| ======================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t11.c1, t11.c2, s13.c1, s13.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t11.c1, t11.c2, s13.c1, s13.c2)]), filter(nil), rowset=16 |
| dop=2 |
| 2 - output([t11.c2], [s13.c2], [t11.c1], [s13.c1]), filter(nil), rowset=16 |
| equal_conds([t11.c2 = s13.c2]), other_conds(nil) |
| 3 - output([t11.c2], [t11.c1]), filter(nil), rowset=16 |
| 4 - output([t11.c2], [t11.c1]), filter(nil), rowset=16 |
| dop=2 |
| 5 - output([t11.c1], [t11.c2]), filter(nil), rowset=16 |
| 6 - output([t11.c1], [t11.c2]), filter(nil), rowset=16 |
| access([t11.c1], [t11.c2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([t11.__pk_increment]), range(MIN ; MAX)always true |
| 7 - output([s13.c1], [s13.c2]), filter(nil), rowset=16 |
| 8 - output([s13.c1], [s13.c2]), filter(nil), rowset=16 |
| access([s13.c1], [s13.c2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([s13.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------------------------+
In the execution plan of the preceding example, the outputs & filters section shows the information of the EXCH-IN/OUT (BC2HOST) operator. The field meanings are the same as those of the EXCH-IN/OUT operator.