All Products
Search
Document Center

SUBPLAN SCAN

Last Updated: Jun 18, 2021

The SUBPLAN SCAN operator shows the view from which the data is accessed by the optimizer.

When 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 it 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 (0.12 sec)

obclient>INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected (0.12 sec)

obclient>CREATE VIEW v AS SELECT * FROM t1 LIMIT 5;
Query OK, 1 rows affected (0.12 sec)

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 only be used 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.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.

Such asv.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.

When FROM TABLE is a view, and view merging rewrite can be performed at the specified conditions, the SUBPLAN SCAN operator does not appear in the execution plan. Compared 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)