The SUBPLAN SCAN operator shows the view from which data is accessed by the optimizer.
If FROM TABLE in a query is a view, a SUBPLAN SCAN operator is assigned in the execution plan. The SUBPLAN SCAN operator is similar to the TABLE SCAN operator, except that SUBPLAN SCAN does not read data from the base table. Instead, it reads the output data of subnodes.
In the following example, the No. 1 operator in query Q1 is generated by the query in the view, and SUBPLAN SCAN, the No. 0 operator, reads the No. 1 operator and generates the output data.
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected
obclient> CREATE VIEW v AS SELECT * FROM t1 LIMIT 5;
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT * FROM V WHERE c1 > 0\G
*************************** 1. row ***************************
Query Plan:
=====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-------------------------------------
|0 |SUBPLAN SCAN|v |1 |37 |
|1 | TABLE SCAN |t1 |2 |37 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([v.c1], [v.c2]), filter([v.c1 > 0]),
access([v.c1], [v.c2])
1 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0),
limit(5), offset(nil)
Note
The LIMIT operator can be used only in SQL queries in MySQL mode. For more information, see LIMIT.
In the preceding example, the outputs & filters section in the execution plan display of query Q1 shows in detail the output information of the SUBPLAN SCAN operator.
| Parameter | Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. such as v.c 1> 0 in filter([v.c 1> 0]). |
| access | The name of the column to be used. The operator reads it from the subnode. |
If FROM TABLE is a view, and view merge rewriting can be performed at the specified conditions, the SUBPLAN SCAN operator does not appear in the execution plan. In comparison with query Q1, the filter condition is not specified for query Q2 in the following example. The SUBPLAN SCAN operator is not required.
Q2:
obclient> EXPLAIN SELECT * FROM v\G
*************************** 1. row ***************************
Query Plan:
===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |2 |37 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0),
limit(5), offset(nil)