The MATERIAL operator materializes the data output of subsequent operators.
OceanBase Database executes plans based on 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 dispense with the need for repeated execution of a subplan.
In the following example, when a NESTED-LOOP JOIN operator is executed on tables t1 and t2, repeated scans are required for the table on the right side. In this case, a MATERIAL operator can 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
obclient> CREATE TABLE t2(c1 INT ,c2 INT ,c3 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+ORDERED USE_NL(T2)*/* FROM t1,t2 WHERE t1.c1=t2.c1;
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, Operator 2 MATERIAL in the execution plan saves data of table t2 to dispense with the need for scanning table t2 from the disk for every join operation. The Outputs & filters section shows in detail the output information of the MATERIAL operator.
| Field | Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the MATERIAL operator. |