The SUBPLAN FILTER operator drives the execution of subqueries in expressions.
OceanBase Database executes the SUBPLAN FILTER operator based on 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
obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected
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 No. 0 operator SUBPLAN FILTER in the execution plan display drives 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.
| Parameter | Description |
|---|---|
| output | The output columns 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 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 because the SUBPLAN FILTER operator drives 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 subquery(1) parameter 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 init_plan_ returns multiple rows and columns, while onetime_expr_ returns only one row and one column. In this example, this 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:
The
SUBPLAN FILTERoperator starts by executingonetime_exprs_.It obtains the result of the non-correlated subquery on the right from the parameter set and pushes
filterdown to the left subplan to execute the left subquery.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 No. 0 operator SUBPLAN FILTER in the execution plan display drives 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.
| Parameter | 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 subquery(1) parameter indicates that onetime_exprs_ applies to the first subplan on the right of SUBPLAN FILTER. In this example, this 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 init_plan_ returns multiple rows and columns, while onetime_expr_ returns only one row and one column. In this example, this 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:
The
SUBPLAN FILTERoperator starts by executingonetime_exprs_.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.
It then executes
filterto export rows of data that meet the condition.