All Products
Search
Document Center

GI

Last Updated: Jun 18, 2021

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, GI operators 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.