All Products
Search
Document Center

MATERIAL

Last Updated: Jun 18, 2021

The MATERIAL operator materializes the data output of subsequent operators.

OceanBase Database executes a plan by streaming data. Sometimes, however, an operator cannot execute a plan until subsequent operators have all data output. Therefore, a MATERIAL operator is added to materialize all the data. The MATERIAL operator can also avoid the repeated execution of a subplan when the subplan needs to be executed repeatedly.

In the following example, when a NESTED LOOP JOIN operation is performed on tables t1 and t2, repeated scans are required for the table on the right side. So, a MATERIAL operator could be added to the table on the right side to save all data of table t2.

obclient>CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2(c1 INT ,c2 INT ,c3 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>EXPLAIN SELECT  /*+ORDERED USE_NL(T2)*/* FROM t1,t2 
         WHERE t1.c1=t2.c1\G;
*************************** 1. row ***************************
Query Plan: 
===========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST  |
-------------------------------------------
|0 |NESTED-LOOP JOIN|    |2970     |277377|
|1 | TABLE SCAN     |t1  |3        |37    |
|2 | MATERIAL       |    |100000   |176342|
|3 |  TABLE SCAN    |t2  |100000   |70683 |
===========================================
Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3], [t2.c1], [t2.c2], [t2.c3]), filter(nil), 
      conds([t1.c1 = t2.c1]), nl_params_(nil)
  1 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), 
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p0)
  2 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil)
  3 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), 
      access([t2.c1], [t2.c2], [t2.c3]), partitions(p0)

In the preceding example, the function of MATERIAL, the No. 2 operator in the execution plan display, is to save data of table t2 to avoid scanning table t2 from the disk for every JOIN operation. The Outputs & filters section in the execution plan display shows in detail the output information of the MATERIAL operator.

Field

Description

output

The output expression of the operator.

rownum() indicates the expression corresponding to ROWNUM.

filter

The filter conditions of the operator.

In this example, the condition is set to nil because no filter is configured for the MATERIAL operator.