All Products
Search
Document Center

SUBPLAN FILTER

Last Updated: Jun 18, 2021

The SUBPLAN FILTER operator drives the execution of subqueries in expressions.

OceanBase Database executes the SUBPLAN FILTER operator by the NESTED-LOOP algorithm, where a row of data on the left is taken to execute a subplan on the right. The SUBPLAN FILTER operator can drive the execution of correlated and non-correlated subqueries, but in different ways.

Drive the execution of a non-correlated subquery

Example 1: The SUBPLAN FILTER operator drives the execution of a non-correlated subquery

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

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

obclient>EXPLAIN SELECT /*+NO_REWRITE*/c1 FROM t1 WHERE 
        c2 > (SELECT MAX(c2) FROM t2)\G;
*************************** 1. row ***************************
Query Plan: 
| ===========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST  |
-------------------------------------------
|0 |SUBPLAN FILTER  |    |33334    |167652|
|1 | TABLE SCAN     |T1  |100000   |68478 |
|2 | SCALAR GROUP BY|    |1        |85373 |
|3 |  TABLE SCAN    |T2  |100000   |66272 |
===========================================
Outputs & filters: 
-------------------------------------
  0 - output([T1.C1]), filter(nil), 
      exec_params_(nil), onetime_exprs_([subquery(1)]), init_plan_idxs_(nil)
  1 - output([T1.C1]), filter([T1.C2 > ?]), 
      access([T1.C2], [T1.C1]), partitions(p0)
  2 - output([T_FUN_MAX(T2.C2)]), filter(nil), 
      group(nil), agg_func([T_FUN_MAX(T2.C2)])
  3 - output([T2.C2]), filter(nil), 
      access([T2.C2]), partitions(p0)

In the preceding example, the function of SUBPLAN FILTER, the No. 0 operator in the execution plan display, is to drive the execution of SCALAR GROUP BY subplans on the right. The Outputs & filters section shows in detail the output information of the SUBPLAN FILTER operator.

Field

Description

output

The output columns of the operator.

filter

The filter conditions of the operator.

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

exec_params_

A parameter of the left subplan on which the execution of the right subplan depends. During execution, the SUBPLAN FILTER operator obtains the parameter from the left subplan and passes it to the right subplan for execution.

In this example, the parameter is set to nil as the SUBPLAN FILTER operator is driving a non-correlated subquery.

onetime_exprs_

An expression that is calculated only once in the execution plan. If the right subplan is for a non-correlated subquery, its result would be the same each time it is executed. So, the result is saved to the parameter set after the first execution.

Upon each execution of the SUBPLAN FILTER operator, this result may be directly taken from the parameter set to serve as the execution result of the right subplan . The parameter subquery(1) indicates that onetime_exprs_ applies to the first subplan on the right of SUBPLAN FILTER.

init_plan_ids_

A subplan that needs to be executed only once in the operator.

init_plan_ differs from onetime_exprs_ in that it returns multiple rows and columns, while onetime_expr_ returns only one row and one column.

In this example, the parameter is set to nil because it is not configured for the SQL query.

Generally, the SUBPLAN FILTER operator drives the execution of a non-correlated subquery by following this process:

  1. The SUBPLAN FILTER operator starts by executing the onetime_exprs_.

  2. It obtains from the parameter set the result of the non-correlated subquery on the right, and pushes the filter down to the left subplan to execute the left subquery.

  3. It then outputs the rows of the left subquery.

Drive the execution of a correlated subquery

Example 2: The SUBPLAN FILTER operator drives the execution of a correlated subquery

obclient>EXPLAIN SELECT /*+NO_REWRITE*/c1 FROM t1 WHERE c2 > (SELECT 
                MAX(c2) FROM t2 WHERE t1.c1=t2.c1)\G;
*************************** 1. row ***************************
Query Plan: 
| ===============================================
|ID|OPERATOR        |NAME|EST. ROWS|COST      |
-----------------------------------------------
|0 |SUBPLAN FILTER  |    |33334    |8541203533|
|1 | TABLE SCAN     |T1  |100000   |68478     |
|2 | SCALAR GROUP BY|    |1        |85412     |
|3 |  TABLE SCAN    |T2  |990      |85222     |
===============================================
Outputs & filters: 
-------------------------------------
  0 - output([T1.C1]), filter([T1.C2 > subquery(1)]), 
      exec_params_([T1.C1]), onetime_exprs_(nil), init_plan_idxs_(nil)
  1 - output([T1.C1], [T1.C2]), filter(nil), 
      access([T1.C1], [T1.C2]), partitions(p0)
  2 - output([T_FUN_MAX(T2.C2)]), filter(nil), 
      group(nil), agg_func([T_FUN_MAX(T2.C2)])
  3 - output([T2.C2]), filter([? = T 2.C 1]), 
      access([T2.C1], [T2.C2]), partitions(p0)

In the preceding example, the function of SUBPLAN FILTER, the No. 0 operator in the execution plan display, is to drive the execution of SCALAR GROUP BY subplans on the right. The Outputs & filters section shows in detail the output information of the SUBPLAN FILTER operator.

Field

Description

output

The output columns of the operator.

filter

The filter conditions of the operator.

For instance, the filter condition for the SQL query in example 2 is t1.c2 > subquery(1).

exec_params_

A parameter of the left subplan on which the execution of the right subplan depends. During execution, the SUBPLAN FILTER operator obtains the parameter from the left subplan and passes it to the right subplan for execution.

These are parameters to be pushed down to the right after a row of output data is exported from the left, and are generally not present for a non-correlated subquery.

onetime_exprs_

An expression that is calculated only once in the execution plan. If the right subplan is for a non-correlated subquery, its result would be the same each time it is executed. So, the result is saved to the parameter set after the first execution.

Upon each execution of the SUBPLAN FILTER operator, this result may be directly taken from the parameter set to serve as the execution result of the right subplan . The parameter subquery(1) indicates that onetime_exprs_ applies to the first subplan on the right of SUBPLAN FILTER.

In this example, the parameter is set to nil because it is not configured for the SQL query.

init_plan_idxs_

A subplan that needs to be executed only once in the operator.

init_plan_ differs from onetime_exprs_ in that it returns multiple rows and columns, while onetime_expr_ returns only one row and one column.

In this example, the parameter is set to nil because it is not configured for the SQL query.

Generally, the SUBPLAN FILTER operator drives the execution of a correlated subquery by following this process:

  1. The SUBPLAN FILTER operator starts by executing the onetime_exprs_.

  2. It executes the left subquery and, after exporting a row, calculates relevant parameters and pushes them down to the right to execute the right subquery.

  3. It then executes the filter to export rows of data that meet the condition.