GI

The GI operator divides a table's data access task into subtasks based on partition or data block granularity, generating multiple parallelizable subtask units to iterate over the entire table.

GI Operator Types

Based on the iteration granularity, OceanBase Database primarily uses two types of GI operators (PX PARTITION ITERATOR and PX BLOCK ITERATOR) to adapt to different query scenarios and performance requirements.

PX PARTITION ITERATOR

The PX PARTITION ITERATOR operator iterates data at the partition granularity.

PX PARTITION ITERATOR Example

1. Create table tbl1.

   obclient> CREATE TABLE tbl1 (col1 INT, col2 INT)
       PARTITION BY HASH(col1) PARTITIONS 4;
   

2. Create index idx_tbl1 on column col1 in table tbl1.

   obclient> CREATE INDEX idx_tbl1 ON tbl1 (col1);
   

3. Q1: Use the FULL hint for a full table scan, query column col1 from table tbl1, and view the execution plan.

   obclient> EXPLAIN SELECT /*+FULL(tbl1)*/ col1 FROM tbl1;
   

   The result is as follows:

   +----------------------------------------------------------------------+
   | Query Plan                                                           |
   +----------------------------------------------------------------------+
   | =============================================================        |
   | |ID|OPERATOR                 |NAME    |EST.ROWS|EST.TIME(us)|        |
   | -------------------------------------------------------------        |
   | |0 |PX COORDINATOR           |        |1       |10          |        |
   | |1 |└─EXCHANGE OUT DISTR     |:EX10000|1       |10          |        |
   | |2 |  └─PX PARTITION ITERATOR|        |1       |9           |        |
   | |3 |    └─TABLE FULL SCAN    |TBL1    |1       |9           |        |
   | =============================================================        |
   | Outputs & filters:                                                   |
   | -------------------------------------                                |
   |   0 - output([INTERNAL_FUNCTION(TBL1.COL1)]), filter(nil), rowset=16 |
   |   1 - output([INTERNAL_FUNCTION(TBL1.COL1)]), filter(nil), rowset=16 |
   |       dop=1                                                          |
   |   2 - output([TBL1.COL1]), filter(nil), rowset=16                    |
   |       force partition granule                                        |
   |   3 - output([TBL1.COL1]), filter(nil), rowset=16                    |
   |       access([TBL1.COL1]), partitions(p[0-3])                        |
   |       is_index_back=false, is_global_index=false,                    |
   |       range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true  |
   +----------------------------------------------------------------------+
   19 rows in set
   

In the Q1 query result, besides the PX PARTITION ITERATOR operator (operator 2), the following execution operators are also displayed:

• PX COORDINATOR and EXCHANGE OUT DISTR: Both operators belong to the EXCHANGE family and are used for inter-thread data exchange. PX COORDINATOR is a special case of EXCHANGE IN, responsible not only for pulling back remote data but also for scheduling subplan execution. For details, see EXCHANGE.
• TABLE FULL SCAN: This operator belongs to the TABLE SCAN family and shows which index (or primary table) the optimizer chooses to access data. For details, see TABLE SCAN.

The Outputs & filters section in the Q1 execution plan display lists the output information for the PX PARTITION ITERATOR operator as follows:

PX BLOCK ITERATOR

The PX BLOCK ITERATOR operator iterates data at the data block granularity.

Compared to PX PARTITION ITERATOR, PX BLOCK ITERATOR operates at a finer granularity, dividing data into more tasks and supporting higher parallelism.

PX BLOCK ITERATOR Example

1. Create table tbl2.

   obclient> CREATE TABLE tbl2 (col1 INT, col2 INT)
       PARTITION BY HASH(col1) PARTITIONS 4;
   

2. Create index idx_tbl2 on column col1 of table tbl2.

   obclient> CREATE INDEX idx_tbl2 ON tbl2 (col1);
   

3. Q2: Use the PARALLEL hint to specify a degree of parallelism of 4, query column col1 of table tbl2, and view the execution plan for this query.

   obclient> EXPLAIN SELECT /*+PARALLEL(4)*/ col1 FROM tbl2;
   

   The return result is as follows:

   +------------------------------------------------------------------------------------------+
   | Query Plan                                                                               |
   +------------------------------------------------------------------------------------------+
   | ===============================================================                          |
   | |ID|OPERATOR             |NAME          |EST.ROWS|EST.TIME(us)|                          |
   | ---------------------------------------------------------------                          |
   | |0 |PX COORDINATOR       |              |1       |1           |                          |
   | |1 |└─EXCHANGE OUT DISTR |:EX10000      |1       |1           |                          |
   | |2 |  └─PX BLOCK ITERATOR|              |1       |1           |                          |
   | |3 |    └─TABLE FULL SCAN|TBL2(IDX_TBL2)|1       |1           |                          |
   | ===============================================================                          |
   | Outputs & filters:                                                                       |
   | -------------------------------------                                                    |
   |   0 - output([INTERNAL_FUNCTION(TBL2.COL1)]), filter(nil), rowset=16                     |
   |   1 - output([INTERNAL_FUNCTION(TBL2.COL1)]), filter(nil), rowset=16                     |
   |       dop=4                                                                              |
   |   2 - output([TBL2.COL1]), filter(nil), rowset=16                                        |
   |   3 - output([TBL2.COL1]), filter(nil), rowset=16                                        |
   |       access([TBL2.COL1]), partitions(p0)                                                |
   |       is_index_back=false, is_global_index=true,                                         |
   |       range_key([TBL2.COL1], [TBL2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true |
   +------------------------------------------------------------------------------------------+
   18 rows in set
   

The Outputs & filters section in the execution plan display for Q2 above lists in detail the output information for the PX BLOCK ITERATOR operator. The meaning of the fields is the same as that for the PX PARTITION ITERATOR operator.