The GI operators are used in parallel execution to iterate a table by partition or by data block.
Based on the granularity of data iteration, GIoperators include PX PARTITION ITERATOR and PX BLOCK ITERATOR.
PX PARTITION ITERATOR
The PX PARTITION ITERATOR operator iterates data at partition level.
In the following example, the No. 2 operator iterates data at partition level.
obclient>CREATE TABLE t (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.12 sec)
obclient>CREATE INDEX idx ON t (c1);
Query OK, 0 rows affected (0.12 sec)
obclient>EXPLAIN SELECT /*+FULL(t)*/ c1 FROM t\G;
*************************** 1. row ***************************
Query Plan:
======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------
|0 |PX COORDINATOR | |400000 |427257|
|1 | EXCHANGE OUT DISTR |:EX10000|400000 |247403|
|2 | PX PARTITION ITERATOR| |400000 |247403|
|3 | TABLE SCAN |T |400000 |247403|
======================================================
Outputs & filters:
-------------------------------------
0 - output([T.C1], [T.C2]), filter(nil)
1 - output([T.C1], [T.C2]), filter(nil), dop=1
2 - output([T.C1], [T.C2]), filter(nil)
3 - output([T.C1], [T.C2]), filter(nil),
access([T.C1], [T.C2]), partitions(p[0-3])
In the preceding example, the Outputs & filters section in the execution plan display shows in detail the output information of the PX PARTITION ITERATOR operator.
| Field | Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. In this example, the filter is set to nil because no filter condition is configured for the PX PARTITION ITERATOR operator. |
PX BLOCK ITERATOR
The PX BLOCK ITERATOR operator iterates data at block level.
The PX BLOCK ITERATOR iterates data at block level, which is a finer granularity compared with the PX PARTITION ITERATOR operator. Therefore, it has finer task division, and supports a higher degree of parallelism.
obclient>EXPLAIN SELECT /*+PARALLEL(4)*/ c1 FROM t\G;
*************************** 1. row ***************************
Query Plan:
==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------
|0 |PX COORDINATOR | |400000 |279171|
|1 | EXCHANGE OUT DISTR|:EX10000|400000 |189244|
|2 | PX BLOCK ITERATOR| |400000 |189244|
|3 | TABLE SCAN |T(IDX) |400000 |189244|
==================================================
Outputs & filters:
-------------------------------------
0 - output([T.C1]), filter(nil)
1 - output([T.C1]), filter(nil), dop=4
2 - output([T.C1]), filter(nil)
3 - output([T.C1]), filter(nil),
access([T.C1]), partitions(p[0-3])
In the preceding example, the Outputs & filters section in the execution plan display shows in detail the output information of the PX BLOCK ITERATOR operator. Fields of this operator have the same meaning as those of the PX PARTITION ITERATOR operator.